excel - How to split data in one column into multiple columns based on duplicates in another column

Question

Is there a way to split data in one column into separate columns based on a duplicate cell, so that I only see 1 row for the data ex:

Column A   Column B    Column C

Gary          1         Gold

Gary          1         Silver

Gary          1         Bronze

How I need it to display:

Column A    Column B    Column C   Column D    Column E

Gary          1         Gold       Silver      Bronze

Any help would be great !

Answer 1

Explanation - First table is the original table, paste unique names ( Gary, Ben) as transpose in the next rows , insert the index match formula in cell below and drag down,write similar formula in next column and drag down.

use this formula INDEX($C:$C,MATCH(F$2,$A:$A,0)+ROW(1:1)-1,0) or look for offset formula. Column C contains gold,silver detail and column a contains names. F2 is gary pasted as transpose below

Gary    Ben [ transpose of all unique names]
formula formula
formula formula

Then copy paste table e:f as transpose values to get the desired layout