Concatenate last column for rows with same ID

[bongielondy]

Hi,

I would like to have text in column L concatenated based on the same ID

ID Status CSE EntryDate Serial Address1 Area Fault PurchaseOrder Customer line Data
1234 Complete D12Livy 2016/08/30 FT00682 Malparl 3 Not reading 9176 Statto 1 New data
1234 InComplete D12Livy 2016/08/30 FT00682 Malparl 3 Not reading 9176 Statto 1 New data
1234 InComplete D12Livy 2016/08/30 FT00682 Malparl 3 Not reading 9176 Statto 8 31/08/2019 10:00 Tech currently at Mulgrave
1234 InComplete D12Livy 2016/08/30 FT00682 Malparl 3 Not reading 9176 Statto 9 31/08/2016 12:52 Tech currently at Key West
1234 InComplete D12Livy 2016/08/30 FT00682 Malparl 3 Not reading 9176 Statto 11 incomplete call machine freezing
458 InComplete D70 2016/08/31 FT785 Terrace 4 Freezing 8111 Statto 4 New data
458 InComplete D70 2016/08/31 FT785 Terrace 4 Freezing 8111 Statto 7 Tech on site
458 InComplete D70 2016/08/31 FT785 Terrace 4 Freezing 8111 Statto 8 Tech to order part

Result for ID 1234 must be;

ID Status CSE EntryDate Serial Address1 Area Fault PurchaseOrder Customer line Data
1234 Complete D12Livy 2016/08/30 FT00682 Malparl 3 Not reading 9176 Statto 1 New data, 31/08/2019 10:00 Tech currently at Mulgrave, 31/08/2016 12:52 Tech currently at Key West, incomplete call machine freezing

[Glenn Kennedy]

can you post an Excel sheet, as it's not obvious from your post where one column ends and the next begins...

Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[DBY]

@Glenn
This is a double post. The one with the file attachment is here:

http://www.excelforum.com/excel-prog...h-same-id.html

[daffodil11]

You could do this nicely with an INDEX(MATCH(INDEX to isolate unique values of A, INDEX(MATCH to return columns of A's match, and then a concatenation UDF to pull in unique values of L for each A.

A: =INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($A$12:A12,$A$2:$A$9),0,0),0))
B: =INDEX(B$2:B$9,MATCH($A13,$A$2:$A$9,0))
L: =ConcatIf($A$2:$A$9,A13,$L$2:$L$9,", ",1)

ConcatIf parameters: =CONCATIF(criteria_range, criteria, concatenation_range, delimiter, boolean for unique values)

[jindon]

Try

Please Login or Register  to view this content.

[bongielondy]

To Jindon, the Forum Guru, Thank you so much. The test macro works so easily. You are a Guru indeed.
Thank you also to daffodil11 , DBY and Glenn Kennedy for your contributions.
This is the best online Excel Forum.
I don't have enough words to thank you

[jindon]

bongielondy

You are welcome and thanks for the PM. (no need to do it though)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.