Deduplicate and merge

Hello,

I have a table like this

A Joe
A John
B Bob
C Diana
D James
E Jack
E Lou
E Brad
E Fred

and would like to reach the following result

A Joe, John
B Bob
C Diana
D James
E Jack, Lou, Brad, Fred

I did not manage to find the right formula. Please advise if you have ideas. Many thanks, Tiger

Will you put the unique letters (A, B, C etc) in column F, or do you want a formula to extract those as well?

How many names (maximum) are you likely to have for each individual letter?

Pete

Thanks Peter for your feedback.
I could easily put the unique letters in column F myself using the deduplicate function from Excel 2010. I guess I will have a maximum of 10 names for each individual letter.

See the attached

Okay, I've put this formula in E7:

Formula:

Please Login or Register  to view this content.

and copied it down beyond your data.

Then I put this formula in G7:

Formula:

Please Login or Register  to view this content.

and copied this down. This only returns up to 5 names, but hopefully you can see what needs to be done to increase this to more - you should highlight just this part of the formula in the formula bar:

&IF(ISNA(MATCH(F7&"_5",E:E,0)),"",", "&INDEX(D:D,MATCH(F7&"_5",E:E,0)))

then click <copy>, then move the cursor to the end of the formula in the formula bar and do CTRL-V (paste) a few times. Then you need to change the 5 (shown above in red) to 6, then 7 then 8 etc for each time that you have pasted the expression in.

Then you can copy the longer formula down.

Hope this helps.

Pete

Many thanks Pete. It works like a charm!
All the best,
Tiger.

Teethless Mama,

This also works very well. Many thanks. Tiger.