Skip to content

Instantly share code, notes, and snippets.

@cypok
Last active August 29, 2015 14:09
Show Gist options
  • Save cypok/bb6979fa535f3ab10874 to your computer and use it in GitHub Desktop.
Save cypok/bb6979fa535f3ab10874 to your computer and use it in GitHub Desktop.
OpenOffice.org Calc macro ConcatenateIf: mix of CountIf and Concatenate
REM Usage example:
REM
REM | A | B |
REM --+-----+-----+
REM 1 | 111 | aaa |
REM 2 | 222 | bbb |
REM 3 | 222 | ccc |
REM 4 | 333 | ddd |
REM 5 | 333 | eee |
REM 6 | 222 | fff |
REM 7 | 111 | ggg |
REM
REM =CONCATENATEIF(A1:A7;222;B1:B7;", ") => "bbb, ccc, fff"
Function ConcatenateIf(CriteriaRange As Variant, Condition As Variant, ConcatenateRange As Variant, Separator As String) As Variant
Dim i As Long
Dim j As Long
Dim strResult As String
On Error GoTo ErrHandler
If Not (IsArray(CriteriaRange) And _
IsArray(ConcatenateRange) And _
ubound(CriteriaRange, 1) = ubound(ConcatenateRange, 1) And _
ubound(CriteriaRange, 2) = ubound(ConcatenateRange, 2)) Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To ubound(CriteriaRange, 1)
For j = 1 To ubound(CriteriaRange, 2)
If CriteriaRange(i, j) = Condition Then
strResult = strResult & Separator & ConcatenateRange(i, j)
End If
Next j
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment