Pivot table question

[SueBK]

Can you tell me how to set up a pivot table to match up lists? I did it yesterday, but obviously laid out my data differently and I can't replicate it today. (No, I didn't save it; it was so simple I didn't think I'd need it again.)

I have a list of 584 column headings that is my full data set. I then have various subsets of this data. I want to see what headings match up across the various datasets, which may help me cull out unnecessary fields.

It annoys me that I can't work out how I set up the Pivot Table yesterday.

Data source:
All Headings.... Subsheet 1.... Subsheet 2
Heading 1.......... Heading 1 .......Heading 1
Heading 2 ..........Heading 2....... Heading 3
Heading 3 .......... .......... .......... ..........

Desired result:
All headings..... Subsheet 1..... Subsheet 2 .... Grand Total
Heading 1............. 1 .......................1.......................2
Heading 2 .............1 ....................... .......................1
Heading 3............. .......................1.......................1

 

[AlphaFrog]

On the pivot table "Layout" screen...

• All Headings in the "Row" area
• Count of Subsheet 1 in the "Data" area
• Count of Subsheet 2 in the "Data" area
• Nothing in the "Column" area

Edit: I think the data table should be this...
Data source:
All Headings.... Subsheet 1.... Subsheet 2
Heading 1.......... Heading 1 .......Heading 1
Heading 2 ..........Heading 2....... .............
Heading 3 .......... .......... ......... Heading 3

 

[SueBK]

Doesn't work. What I end up with is:

Data source:
All Headings.... Subsheet 1.... Subsheet 2
Heading 1.......... Heading 1 .......Heading 1
Heading 2 ..........Heading 2....... Heading 3
Heading 3 .......... .......... .......... ..........

Result:
All headings..... Subsheet 1..... Subsheet 2 Heading 1............. 1 .......................1
Heading 2 .............1 .......................1
Heading 3............. - .......................-

It just matches across the row, rather than matching the contents. I *thought* that's what I did yesterday!

 

[AlphaFrog]

I don't know how else to explain it.

On your result pivot table, you can grab a pivot dropdown and drag it to another area (row, column, data). Drag the Data pivot to the column area. Double-check that All Headings is still in the Row area.

 

[SueBK]

Sorry; formatting went a little squiffy:

Result:
All headings..... Subsheet 1..... Subsheet 2
Heading 1............. 1 .......................1
Heading 2 .............1 .......................1
Heading 3............. - .......................-

 

[AlphaFrog]

Sorry; formatting went a little squiffy:

Result:
All headings..... Subsheet 1..... Subsheet 2
Heading 1............. 1 .......................1
Heading 2 .............1 .......................1
Heading 3............. - .......................-

See "edit" in post #2 about the data table.

 

[SueBK]

If I'm going to move the headings in the individual columns line up in the rows, I don't need the pivot table. Yesterday, when I waved some magic voo-doo wand at the thing, that's the result that I got. Dang, I wish I hadn't deleted the PT yesterday.

 

[SueBK]

I ended up using vlookups to match up column headings with column alpha-numbers against the master list.

It just annoys me, because I've got the evidence sitting in a spreadsheet that I did this with a pivot table yesterday. Must have crossed my eyes a different way!