Concatenate Multiple Rows with same Unique Identifier

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
Hello,

Have a question for excel. I have a sheet with a large list of records. Each record has an ID, but is not unique due to multiple attributes which need to be accounted for, for each ID. Because these records are split, what I am trying to do is build another column at the end of my spreadsheet which will combine all the data from each unique ID into one cell. I will try to show below. Please notice, the combination of A2:B3 are duplicates, I wanted to include this in the example because I want to make sure if there are duplicates, I want to remove those from the results. I would put carriage returns in the results area.

A
B
C
1
ID
Type
Results
2
A-123
Cat-big
Cat-big
Cat-small
Dog-big
Dog-small
Dog-tiny
3
A-123
Cat-big
Cat-big
Cat-small
Dog-big
Dog-small
Dog-tiny

4
A-123
Cat-small
Cat-big
Cat-small
Dog-big
Dog-small
Dog-tiny

5
A-123
Dog-big
Cat-big
Cat-small
Dog-big
Dog-small
Dog-tiny

6
B-123
Dog-small
Dog-small
Dog-tiny

7
B-123
Dog-tiny
Dog-small
Dog-tiny

8
A-987
Cat-tiny
Cat-tiny
9
A-777
Cat-large
Cat-large
10
A-555
Bull-large
Bull-large
Bull-skinny
11
A-555
Bull-skinny
Bull-large
Bull-skinny


<tbody>
</tbody>
 
You can do it like
Code:
   Set Clm3 = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
  [COLOR=#0000ff] Clm3.Value = "Results"[/COLOR]
   If Clm1 Is Nothing Or Clm2 Is Nothing Then
Also please do not quote entire post, as it just clutters up the board.
 
Last edited:
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Yes if you want to
However in future please do not hijack other peoples threads to ask unrelated questions.
For a question like this you should have posted it to the "About this board" forum
 
Upvote 0
You are correct, I apologize. I did not mean to include those. Dog-small and Dog-tiny are not supposed to be in there.
Here is a UDF (user defined function) that should work for you...
Code:
Function ConCat(ID As String, IDRng As Range, TypeRng As Range) As String
  Dim X As Long, IDData As Variant, TypeData As Variant
  IDData = IDRng
  TypeData = TypeRng
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(IDData)
      If IDData(X, 1) = ID Then .Item(TypeData(X, 1)) = 1
    Next
    ConCat = Join(.Keys, vbLf)
  End With
End Function
This function takes three arguments... the first is the ID number to search for, the second is the range to look for that ID in and the third is the range containing the Types to be concatenated. So, assuming your first ID is in cell A2 and the corresponding Type is in cell B2, you would use this formula...

=ConCat(A2,A$2:A$11,B$2:B$11)

Note the $ signs which are used to freeze the ranges for when you drag the formula down to the bottom of your data.


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 above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function as shown in the example above.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
I am actually getting an Type Mismatch error in the below line:

Set Dic = CreateObject("scripting.dictionary")
Set Clm1 = Range(Clm1.Offset(1), Clm1.End(xlDown))
Set Clm2 = Clm2.Offset(1).Resize(Clm1.Count)
Set Clm3 = Clm3.Offset(1).Resize(Clm1.Count)
For Each Cl In Clm1
i = i + 1
If Not Dic.Exists(Cl.Value) Then
Clm3(i).Value = Clm2(i).Value
Dic.Add Cl.Value, Array(Clm3(i), CreateObject("scripting.dictionary"))
Dic(Cl.Value)(1)(Clm2(i).Value) = Empty
ElseIf Not Dic(Cl.Value)(1).Exists(Clm2(i).Value) Then
Dic(Cl.Value)(0).Value = Dic(Cl.Value)(0).Value & vbLf & Clm2(i).Value
Dic(Cl.Value)(1)(Clm2(i).Value) = Empty
End If
Next Cl
With Clm3
.SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
.Value = .Value
End With
 
Upvote 0
Do any of your cells contain errors?
Also how long are the text strings likely to be?
 
Upvote 0
There is no errors in any of the cells, I check the column. The each text string can be up to 100 characters. It seems like it is getting caught up after the first time the ID and the Type Desc match. If they both match, I just one that occurrence once in the new results cell.
 
Last edited:
Upvote 0
There is no errors in any of the cells, I check the column. The each text string can be up to 100 characters. It seems like it is getting caught up after the first time the ID and the Type Desc match. If they both match, I just one that occurrence once in the new results cell.

Did you try the code I posted in Message #24 yet? If so, did it not work correctly for you? If not, in what way did it not do what you want?
 
Upvote 0
The only way I get a Type mismatch error is if one of the cells contains something like #N/A, #NAME,#VALUE etc
 
Last edited:
Upvote 0
Perfect; found those #N/As. Other part of my code was messed up.

It is working great. So my question is, if I want to run this code on one column, then on another column, I can just combine the two one after the other while changing up the search column and the name of the results column? This is my main goal; because I want to add this to the bottom of my current code; and run it all at once (with the push of a button).
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top