Creating a comma separated list for unique entires

Please let me know how I can achieve the following:

I have this:

NAME	STATE
BOB	PA
BOB	NJ
BOB	DE
TOM	PA
TOM	DE

I need to have this:

NAME	STATE
BOB	PA, NJ, DE
TOM	PA, DE

I have to do this on a very large scale and doing it manually is just not an option... any ideas would be greatly appreciated.

Thanks!

Hi,

Assuming the original table is in A1:Bnn, and is sorted on the name then use a couple of helper columns.

C2:
=IF(A2=A1,C1&","&B2,B2)

D2:
=COUNTIF($A2:$A$6,A2)
change the row 6 to suit

Copy C2:D2 down, filter the list on column D for the value 1. Then copy the filtered rows and copy them wherever.

Regards

Time for VBA

Insert this VBA oode,

Please Login or Register  to view this content.

Then extract unique Name by Advanced Filter & enter them D2 to down. In E2,

=CONCATIF(A:A,D2,B:B,", ",1)

A:A = Name, B:B = State, 1 for exclude duplicate

Adjust the column width & use 'Wrap Text'

Here you go:

Please Login or Register  to view this content.

You will need to still turn on Wrap Text in those cells.

Maybe try this sub:

Please Login or Register  to view this content.