Remove duplicate rows and move the column data to a new column

Hello, I haven't been able to find a great way to do this so I wanted to ask around.

What I have:

ID	Data1
1	A
1	B
2	C

What I need:

ID	Data1	Data2
1	A	B
2	C

Does anyone know of an automated way to do this? Please note, some rows have duplicates and some do not (leads me to think a macro wouldn't be the best way to do this)

So what happens when there are duplicates? It would help if you post a small sample Excel file.

Basically just the above example. If there are two rows for ID# 1, I want to turn it into one row and expand out the columns. I.e., row 2's column B becomes row 1's column C.

Try

=IFERROR(INDEX($B$2:$B$50,SMALL(IF($A$2:$A$50=$E2,ROW($A$2:$A$50)-ROW($A$2)+1,""),COLUMNS($A:A))),"")

Enter with Ctrl+Shift+Enter

Copy across and down as far as required

E2=1,
E3=2

etc

Originally Posted by JohnTopley

Try

=IFERROR(INDEX($B$2:$B$50,SMALL(IF($A$2:$A$50=$E2,ROW($A$2:$A$50)-ROW($A$2)+1,""),COLUMNS($A:A))),"")

Enter with Ctrl+Shift+Enter

Copy across and down as far as required

E2=1,
E3=2

etc

Seems to be turning all 1's regardless.. hmmm
Capture.JPG

=IFERROR(INDEX($B$2:$B$50,SMALL(IF($A$2:$A$50=$A$2,ROW($A$2:$A$50)-ROW($A$2)+1,""),COLUMNS($A:A))),"")

See attached