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?

Please help

Answers


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.


Need Your Help

The graphical elements of UIKit – what format are they?

iphone iphone-sdk-3.0 uikit

For standard interface elements of the Apple's UIKit, how do apple store them? Are they stored as cocca touch code, pdf, images or other?

pointer to integer type modeled on pointer to typedef type

c pointers typedef

For didactic motives, i want to build a pointer to an integer, and i take a model from a pointer to a typedef (working example below in (1) ) but the example at (0) gives the mesage "error: syntax