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>

 

[KB_Miner]

How about something like this. Put the names in the Row Labels, the Awards in the Column labels, and then put Awards a second time in the Values section. It will give you a count of each award for each person. Would that be close to what you're looking for?

 

[ace19852]

I assume Award is in A1 and Name is in B1 and the file is sorted by name and award. So I have allowed for 4 awards and named C1 to F1 as Award1 to Award4. In column G I have a Helper column with G2 = 2, G3 =3, G4 = 4 etc down the file.
In C2 put
=IF(B1<>"Name","",A2)
In C3 put
=IF(MATCH(B2,$B2:$B$7)>MATCH(B3,$B3:$B$7),"",A3)
Copy that down
In D2 put
=IFERROR(IF(AND($C2<>"",$B3=$B2,MATCH($B2,$B2:$B$7)>MATCH($B3,$B3:$B$7),MATCH($B2,$B2:$B$7)<>0),INDIRECT("B"&$G3),""),"")
In E2 put
=IFERROR(IF(AND($C2<>"",$B4=$B2,MATCH($B2,$B2:$B$7)>MATCH($B3,$B3:$B$7),MATCH($B2,$B2:$B$7)<>0),INDIRECT("B"&$G4),""),"")
In F2 put
=IFERROR(IF(AND($C2<>"",$B5=$B2,MATCH($B2,$B2:$B$7)>MATCH($B3,$B3:$B$7),MATCH($B2,$B2:$B$7)<>0),INDIRECT("B"&$G4),""),"")
And copy these down
In G2 put 2 and in G3 put 3 and in G4 put 4 etc down the column
Once this is done you should get the 1^st line with with name only show data. If you copy paste special the results to a new sheet say and sort it to get rid of blank row you will end up with the desired result.

Also If you want more columns move column G over and for every new column see the Bold enlarged numbers above indicating the only change to the formula that would be necessary e.g. if you need 5 award columns the next col G would have B6 in place of B5

Hope that’s makes sense and maybe some1 will come up with a better solution or a VBA solution

Cheers

Ps I do have the complete file if needed but try it yrself 1st

 

[sjakubik]

it makes perfect sense - thank you so much! However, I cannot get it to work. I'm SURE it is human error, as your description was very simple. Arrgh. I'm not sure what else to do.

 

[ace19852]

Well as I have already detailed the solution check my file that I worked from as you have made yr best attempted to get it to work

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

Cheers

 

[sjakubik]

I think I can do that! Thank you!

 

[sjakubik]

Thank you so much, ace19852 . However, I need to have the names of the additional awards next to the people's names, not a repeat of their names. Your spreadsheet reads:

Award	Name	Award1	Award2	Award3	Award4	Helper
NHS Honor Collar	Abraham, Noah	NHS Honor Collar	Abraham, Noah	Abraham, Noah		2

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

But I need it to read:

Award	Name	Award1	Award2	Award3	Award4	Helper
NHS Honor Collar	Abraham, Noah	NHS Honor Collar	SNHS Honor Cord			2

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[ace19852]

Hmmm I thought that's how mine came out sry about that ! I'll check it see whats gone wrong and get back to you asap

Cheers

PS already found problem
slight error

change in D2 "B" to "A"

=IFERROR(IF(AND($C2<>"",$B3=$B2,MATCH($B2,$B2:$B$7)>MATCH($B3,$B3:$B$7),MATCH($B2,$B2:$B$7)<>0),INDIRECT("B"&$G3),""),"")

=IFERROR(IF(AND($C2<>"",$B3=$B2,MATCH($B2,$B2:$B$7)>MATCH($B3,$B3:$B$7),MATCH($B2,$B2:$B$7)<>0),INDIRECT("A"&$G3),""),"")

and also the same place in E2 i.e. "B" to "A" and copy down and that will fix it

Cheers

 

[sjakubik]

Thank you! What about F2? Would this change as well? And what if I have people with more than three awards?

 

[sjakubik]

Do you have a revised sheet you can post? It is still not working. I am getting:

Award	Name
Pride and Perseverance in Choir	Jemi Abraham	Pride and Perseverance in Choir	Pride and Perseverance in Mathematical Models with Applications	Pride and Perseverance in Mathematical Models with Applications
Pride and Perseverance in Mathematical Models with Applications	Jemi Abraham
NHS Honor Collar	Noah Abraham	NHS Honor Collar	Noah Abraham	Noah Abraham

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