Combining Values in Multiple Rows into 1 Row of Comma-Separated Values Based on Duplicates

Hello All,

I have an Excel workbook that contains Supplier ID numbers and Building Reference numbers. One Supplier ID may be listed in multiple rows, and have a unique Building Reference listed next to each entry. I want to combine multiple entries of a distinct Supplier ID into 1 row, and combine the Building Reference numbers into one cell in each row, separated by commas. I have provided an example below, and have attached an Excel workbook in case formatting is an issue. There are 2 separate example sheets for the data I have, and the output I'd like to see.

The Data I Have
A B
Supplier ID Building Reference
100 A
100 B
100 C
250 A
250 Z
600 C
600 G
600 H
600 J

The Format I Want
A B
Supplier ID Building Reference
100 A,B,C
250 A,Z
600 C,G,H,J

If the combined, comma-separated Building References end up being listed on every row of the original sheet, so be it. I can delete duplicate rows afterward.
Any help will be greatly appreciated!

Use column C in your source sheet as a helper column, with this formula in C2:

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,INT(MAX(C$1:C1)/1000)*1000+1000,C1+1))

Copy this down as far as you need it. It will identify each supplier with a number in the thousands, and then identify each Building Reference with a unique sequential number, so that you get 1000, 1001, 1002, 2000, 2001, and so on.

Then in A2 of the second sheet you can have this formula:

=IFERROR(INDEX('The Data I Have'!A:A,MATCH(ROWS('The Data I Have'!$1:1)*1000,'The Data I Have'!C:C,0)),"")

which will return a unique list of Supplier IDs when copied down. In B2 of that sheet you can have this formula:

=IF(A2="","",SUBSTITUTE(IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000,'The Data I Have'!C:C,0))&", ","")&IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000+1,'The Data I Have'!C:C,0))&", ","")&IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000+2,'The Data I Have'!C:C,0))&", ","")&IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000+3,'The Data I Have'!C:C,0))&", ","")&"zzz",", zzz",""))

which might be easier to follow if I put manual line breaks in it, like this:

=IF(A2="","",SUBSTITUTE(
IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000,'The Data I Have'!C:C,0))&", ","")&
IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000+1,'The Data I Have'!C:C,0))&", ","")&
IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000+2,'The Data I Have'!C:C,0))&", ","")&
IFERROR(INDEX('The Data I Have'!B:B,MATCH(ROWS('The Data I Have'!$1:1)*1000+3,'The Data I Have'!C:C,0))&", ","")&
"zzz",", zzz",""))

This will bring up to 4 Building Refs (separated by comma and space) across for each supplier, but hopefully you can see how it can be extended if your real data has more. Again, copy this down as far as you need to. The "zzz" and SUBSTITUTE function gets rid of the final comma-space in any group.

Hope this helps.

Pete

MarvP, thanks for sharing.

This is the video that shows the Table.Column solution in order to get the data grouped together: Redux: Students, Courses, Power Query ...

Thanks, everyone, for your input. I just now had a chance to get back to this. I will look through responses and report back! Thank you.