-
1 Attachment(s)
Concatenate multiple values
-
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
-
Quote:
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.
-
Quote:
Originally Posted by
Rick Rothstein
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.
