# Excel - Filter unique values from Column A and move related values from Column B to other columns

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?

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.