How to merge rows with same id in Excel?

Question

I have my excel database, and there are several rows with the same id, something like that:

id   data    data
a    Tim      Peter
a    Luke     Jim
b    Rob      Tom
b    Alex     Bob

I would like to end up with a table like that:

id   data           data
a    Tim; Luke      Peter; Jim
b    Rob; Alex      Tom, Bob

is that possible?

Answer 1

Assuming the data starts at A2, and id sorted. You may do :

E2  ->  =IF(A3=A2,"",A2)
F2  ->  =IF($A1=$A2,F1& " ; "&B2,B2)
G2  ->  =IF($A1=$A2,G1& " ; "&C2,C2)

and drag downwards. Use filter in column E and you'll get the final consolidated table.

Hope it helps. ( :