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):
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.