Pivot Newbie Needs Quick Help

[sjakubik]

I have the attached spreadsheet with two columns: column A (award) and column B (name). I need the data to be transposed to Name in column A, Award 1 in column B, Award 2 in column C, etc. Copy/transpose won't work because of the repeated names. I know this is a simple pivot thing but I cannot figure it out. Help, wise ones! Thanks! Susanna

Award	Name
NHS Honor Collar	Abraham, Noah
SNHS Honor Cord	Abraham, Noah
SNHS Honor Cord	Abraham, Noah
NHS Honor Collar	Abraham, Noelle
SNHS Honor Cord	Abraham, Noelle
YES Medal	Abraham, Noelle

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[ace19852]

Just can't think atm anyway amended file to include upto 9 awards and can goto down to 5000 lines deep

Have a look

Download Pivot newbie.xlsx from Sendspace.com - send big files the easy way

Cheers

 

[ace19852]

Ok now I got my brain in gear the file is completely sorted. You can drag rows down or columns across to add more awards and people. However you must have at least 3 awards to drag across more columns awards 1 and 2 are custom tailored

Download Pivot newbie final version.xlsx from Sendspace.com - send big files the easy way

So this is a fully automatic sheet

Cheers

 

[ace19852]

In addition to the above I notice another Board regular hiker95 had produce a close VBA solution so I slightly tweaked it and it's on Sheet of the linked file.

The only thing you need to do for that is to copy your 2 columns from Sheet1 to sheet2 but put Sheet1 column B data i.e Award into Sheet2 column A and put Sheet1 column A data i.e Award into Sheet2 column B. Have a look at sheet I have already put your original data in it.

Also once to the data is copied into sheet2 run the macro by pressing the Ctrl and the Q keys together. Once run copy the Award headings over as required

Download Pivot newbie final version.xlsm from Sendspace.com - send big files the easy way

Cheers

 

[ace19852]

Btw way should have added the link to the thread I got the VBA from i.e hiker95 is below in case you are interested

http://see http://www.mrexcel.com/forum/excel-questions/780017-help.html

Cheers