Concatenate multiple values

[Admin]

Hi

Try this UDF.

Function KCONCAT(ByRef ConcatRange, Optional ByVal blnUnique As Boolean = False, _
                                Optional ByVal Delim As String = ",") As String

    ' Function by Krishnakumar @ ExcelFox.com
    
    ' ConcatRange   : Could be either a Range or array
    ' Delim         : By default the delimiter is ","
    ' blnUnique     : If it's TRUE, return only unique values
    ' Limitation    : Works only single row/column or single dimension array
    
    Dim UpperDimension As Long, i As Long, x
    
    If TypeOf ConcatRange Is Range Then
        
        If (ConcatRange.Rows.Count > 1) * (ConcatRange.Columns.Count = 1) Then
            KCONCAT = Join$(Application.Transpose(ConcatRange.Value), Delim)
        ElseIf (ConcatRange.Rows.Count = 1) * (ConcatRange.Columns.Count > 1) Then
            KCONCAT = Join$(Application.Transpose(Application.Transpose(ConcatRange.Value)), Delim)
        ElseIf (ConcatRange.Rows.Count > 1) * (ConcatRange.Columns.Count > 1) Then
            KCONCAT = CVErr(xlErrNA)
        End If
    
    ElseIf IsArray(ConcatRange) Then
        
        On Error Resume Next
        UpperDimension = UBound(ConcatRange, 2)
        On Error GoTo 0
        
        If UpperDimension = 0 Then
            KCONCAT = Join$(Application.Transpose(Application.Transpose(ConcatRange)), Delim)
        Else
            KCONCAT = Join$(Application.Transpose(ConcatRange), Delim)
        End If
        
        KCONCAT = Replace(Replace(KCONCAT, Delim & "False", ""), "False" & Delim, "")
    
    End If
    If blnUnique Then
        With CreateObject("scripting.dictionary")
            .comparemode = 1
            x = Split(KCONCAT, Delim)
            For i = 0 To UBound(x)
                .Item(x(i)) = Empty
            Next
            If .Count Then KCONCAT = Join$(.keys, Delim)
        End With
    End If
    
End Function

and try

in E2 and copied down,

=KCONCAT(IF($B$2:$B$11=B2,$A$2:$A$11),,", ")

It's an array formula. Conformed with CTRL + SHIFT + ENTER


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA