Pivot Table Questions

[original.grace]

This is sort of complicated.

I have the following spreadsheet:

Code Film Character(s) Count
using the Code
01HL1.10 Toy Story 3 Buzz 1
01HL2.10 Cinderella Cinderella 1
01HL3.10 Monsters Inc Mike, Randall 2
01HL4.10 Monsters Inc Mike 1
01HL5.10 Toy Story 3
01HL6.10 Wall E Eve, narrator, Wall E 3
01HL7.10 Wall E narrator, Wall E 2
01HL8.10 Toy Story 3 Buzz 1
01HL9.10 Toy Story 3 Woody 1

When I make a pivot table for "Count per Code," it gives me the following:

Buzz 2
Cinderella 1
Mike, Randall 1
Mike 1
Eve, narrator, Wall E 1
narrator, Wall E 1
Woody 1

What I really want is:

Buzz 2
Woody 1
Cinderella 1
Mike 2
Randall 1
Eve 1
narrator 2
Wall E 2

How do I do this?

Also, is there a way to do a more in depth analysis. I want to look at a specific film, have a list of all the characters and count the number of codes that character used. For instance, I want the following table:

Reporter field - Wall E (film)
Eve 1
narrator 2
Wall E 2

Whereas right now, I get

Reporter field - Wall E (film)
Eve, narrator, Wall E 1
narrator, Wall E 1


Thanks in advance!!!

 

[original.grace]

Sorry the formatting went all wonky