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

[TABLE="width: 242"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Award[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]NHS Honor Collar[/TD]
[TD]Abraham, Noah[/TD]
[/TR]
[TR]
[TD]SNHS Honor Cord[/TD]
[TD]Abraham, Noah[/TD]
[/TR]
[TR]
[TD]SNHS Honor Cord[/TD]
[TD]Abraham, Noah[/TD]
[/TR]
[TR]
[TD]NHS Honor Collar[/TD]
[TD]Abraham, Noelle[/TD]
[/TR]
[TR]
[TD]SNHS Honor Cord[/TD]
[TD]Abraham, Noelle[/TD]
[/TR]
[TR]
[TD]YES Medal[/TD]
[TD]Abraham, Noelle[/TD]
[/TR]
</tbody>[/TABLE]

 

[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