Excel VBA Function to concatenate results separated by comma

[gailb]

I would like to concatenate the values from range(A2:B13) if they meet the criteria in D2:D7. As you can see from this example, E7 should show nothing since there isn't any match for the 6 in D7. Here is what I'm currently using, but I'm open to any function that will do this in VBA.

Code: Select all

Option Explicit
Dim First As Boolean
Dim c As Variant

Function ConcatY(FinalRng As Variant) As Variant
First = True
ConcatY = "-"
For Each c In FinalRng
    If c <> "" Then
        If First = True Then
            ConcatY = c
            First = False
        Else
            ConcatY = ConcatY & ", " & c
        End If
    End If
Next c
End Function

[StuartR]

Have a look at the concatenateif function in this thread.

[gailb]

Hi Stuart,

This works, but it still concatenates the last value even though the match is empty.

=ConcatenateIf($A$2:$A$13,D2,$B$2:$B$13," / ")

Jan / Feb
Mar / Apr
May / Jun
Jul / Aug
Sep / Oct
/

How can I get rid of that last slash?

[HansV]

Here is a version that skips blanks:

Code: Select all

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            If ConcatenateRange.Cells(i).Value <> "" Then
                strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
            End If
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Use the same way as above.

[gailb]

Perfect. Thanks Hans.

[p45cal]

What version of Excel do you have?
Perhaps builtin functions only with:
=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$13,$A$2:$A$13=D2,""))

[p45cal]

I received a message there was a new post by HansV here but there's nothing?

[HansV]

That is because Niranjanmeyda posted a new message in this thread that actually belonged in this one. I replied, but then moved the posts to the other thread. Sorry for the confusion!