Concatenate multiple values

[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.

372

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_- (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_-)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[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 (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

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 (http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/)

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.

[mcpizzle]

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

LookUp Value and Concatenate All Found Results (http://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/)


Brilliant. My first UDF. I grokked the code of Rick's a bit more than Admin's, so I started there, and it worked like a charm.