Convert 2-Column Spreadsheet to Multi-Columns based on 1 Column

[jdegeorge]

Hi

I have a 2-column spreadsheet, where the values in column 1 repeat but the values in column 2 are unique.

Column 1 Column 2
ADD Joe
ADD Pete
ADD Harry
ADD Tom
ADD Jenny
BEN Joe
BEN Pete
BEN Tom
BEN Jenny
CAD Joe
CAD Pete
CAD Tom
CAD Harry

I want to flip it so Column 1 becomes the Column headings for as many as needed (no repeats) and then the values of COlumn 2 fall in line where necessary.

ADD BEN CAD
Joe Joe Joe
Pete Pete Pete
Harry Harry
Tom Tom Tom
Jenny Jenny


I tried PIVOT TABLES, but only get this:

ADD BEN CAD
Joe
Pete
Harry
Tom
Jenny

(no matter how many times I edit this post, this last table doesn't appear correctly. The names are showing up as column 1 and ADD, BEN and CAD are showing as the headers for Columns 2 through 4. I hope this makes sense.)

The TRANSPOSE function didn't work either.

There are over 3,700 rows for this spreadsheet and can't even imagine attempting this manually! :-(

I'd really appreciate any help on this.

Thanks!
Jim

[JieJenn]

Try

Please Login or Register  to view this content.

[jdegeorge]

JieJenn

Thanks for the quick response, but I haven't a clue what to do with your code. Basically, if Excel doesn't have a built-in function button I'm at a loss.

Jim

[ChemistB]

A formula approach
Step 1: Copy Col A to E1, Copy Col B to F2
Step 2: Data tab> Remove Duplicates on E and then on F
Copy Unique values from E to G1:?1 using Paste Special > transpose
Step 3: In G2, dragged across and down, =IF(COUNTIFS($A$1:$A$13,G$1,$B$1:$B$13,$F2)>0,$F2,"")
Step 4: Copy, Paste Special Values over new table to remove formulas
See attachment

[ChemistB]

Nice code approach Jie

[JieJenn]

Oh my bad. Hit Alt + F11 to open the VBA window, on the menu on top, click on insert > module. Then just copy and paste the code and hit F5 (or click on Macro and select the procedure and hit run) to run the macro.

[JieJenn]

Nice code approach Jie

More like I am too lazy to come up with a formula

[ChemistB]

I spoke too soon. I get the following when I run your code

Please Login or Register  to view this content.

Somethings amiss.

[JieJenn]

Good catch. Just came back from lunch. Here try the revised code

Please Login or Register  to view this content.

[jdegeorge]

Thanks, but your attached file puts each person in each column D through F, when that's not what's represented in columns A and B.

[JieJenn]

Maybe I misunderstand your question. Can you upload a sample file with what the final result should look like.

[mike7952]

Give this a try

Please Login or Register  to view this content.

[ChemistB]

Nope, Harry shouldn't appear under BEN, and Jenny shouldn't appear under CAD. Unless my formulas are wrong

[mike7952]

Heres as sample workbook

Transpose.xlsm

[mike7952]

@JieJenn

Heres an update to your code that should work

Please Login or Register  to view this content.

[jdegeorge]

A formula approach
Step 1: Copy Col A to E1, Copy Col B to F2
Step 2: Data tab> Remove Duplicates on E and then on F
Copy Unique values from E to G1:?1 using Paste Special > transpose
Step 3: In G2, dragged across and down, =IF(COUNTIFS($A$1:$A$13,G$1,$B$1:$B$13,$F2)>0,$F2,"")
Step 4: Copy, Paste Special Values over new table to remove formulas
See attachment

I appreciate everyone's help, but I ran with ChemistB's solution because it looked more familiar. And...it worked! Thanks a bunch!