Concatenate multiple values

**mcpizzle** · 08-25-2012, 05:07 PM

**Admin** · 08-26-2012, 07:26 PM

Hi

Try this UDF.

Code:

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

**Rick Rothstein** · 08-26-2012, 08:44 PM

Originally Posted by mcpizzle

super helpful...I'm attempting to do a modification of this.

I'd like to concatenate ID numbers from all related rows within the table itself. See the attachment.

Counting and Summing for all related values is in the table (see column D), but a reporting of the values themselves? Unsure how to do this. Any great ideas? Thanks.

You can use the UDF (user defined function) that I posted here to do that...

LookUp Value and Concatenate All Found Results

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the code from the above linked article into the code window that just opened up. That's it.... you are done. You can now use LookUpConcat just like it was a built-in Excel function. For the question you asked, put this formula in E2 and copy it down to E11...

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

There are a couple of other optional parameters available for this function which do not appear to be needed for the question you asked, but you should read the above linked article to see what they are in case things change for your situation.