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]

 

[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:

[TABLE="width: 699"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Award[/TD]
[TD]Name[/TD]
[TD]Award1[/TD]
[TD]Award2[/TD]
[TD]Award3[/TD]
[TD]Award4[/TD]
[TD]Helper[/TD]
[/TR]
[TR]
[TD]NHS Honor Collar[/TD]
[TD]Abraham, Noah[/TD]
[TD]NHS Honor Collar[/TD]
[TD]Abraham, Noah[/TD]
[TD]Abraham, Noah[/TD]
[TD][/TD]
[TD]2



[/TD]
[/TR]
</tbody>[/TABLE]
But I need it to read:
[TABLE="width: 699"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Award[/TD]
[TD]Name[/TD]
[TD]Award1[/TD]
[TD]Award2[/TD]
[TD]Award3[/TD]
[TD]Award4[/TD]
[TD]Helper[/TD]
[/TR]
[TR]
[TD]NHS Honor Collar[/TD]
[TD]Abraham, Noah[/TD]
[TD]NHS Honor Collar[/TD]
[TD]SNHS Honor Cord[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

[TABLE="width: 987"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Award[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pride and Perseverance in Choir[/TD]
[TD]Jemi Abraham[/TD]
[TD]Pride and Perseverance in Choir[/TD]
[TD]Pride and Perseverance in Mathematical Models with Applications[/TD]
[TD]Pride and Perseverance in Mathematical Models with Applications[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pride and Perseverance in Mathematical Models with Applications[/TD]
[TD]Jemi Abraham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NHS Honor Collar[/TD]
[TD]Noah Abraham[/TD]
[TD]NHS Honor Collar[/TD]
[TD]Noah Abraham[/TD]
[TD]Noah Abraham[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]