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>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
Fluff - is it permitted to put a spreadsheet on google sheets and give access to all ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
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.
 

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Do any of your cells contain errors?
Also how long are the text strings likely to be?
 

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186

ADVERTISEMENT

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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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:

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,771
Members
416,202
Latest member
donya ba

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
Top