Excel - Filter unique values from Column A and mov

Question

I have a excel like this

Col1 - Col2
A - 1
B - 2
A - 3
B - 4
A - 5

I want to produce an excel from above one as shown below

Col1 - Col2 - Col3 - Col4
A - 1 - 3 - 5
B - 2 - 4

Is there a way in excel to do this?

Please help

Answer 1

Here's a way to do it with formulas. It takes a small bit of formatting first though, so perhaps copy your data to a new sheet, then tweak.

First, get the unique values from Column A. In your example, these would be A and B. (For long lists, copy column A to some random column. Select that column, go to Data--> Remove Duplicates. This will give you your unique list).

After you have the list, place it in Column D (for example). Then, enter this formula as an array in column E (enter with CTRL+SHIFT+ENTER):

=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D1,ROW($A$1:$A$6)-ROW($A$1)+1),ROWS(INDIRECT("$A$1:A"&COLUMN()-4)))),"")

A couple notes: Adjust the range from rows 1 to 6 as necessary. Also, if you choose a column other than column E to put this in, adjust the Column()-4 part at the end so that the column number minus the other number (in my example, 4) equals 1. (In my formula the Column() returns 5 (for "E") and 5-4=1.

You can then drag over, and down, and it should work.