?
Solved

Comparing Results of Pivot Tables

Posted on 2012-08-10
7
Medium Priority
?
504 Views
Last Modified: 2012-08-18
Hi Experts,

Is there away to compare the results of pivot tables?

Specifically, I had four data files which I imported to Excel (one data file per worksheet) and created four sets of pivot tables.

For example, I have four pivot tables (one for each extract file) which displays the names of individuals associated to a record.   As such, is there a simple way (either through Excel commands or macros) that I can compare and create a tabular summary where I list all the unique names and indicate which extract has them?

Example output
                       PivotTable1                       PivotTable2                       PivotTable3
Name1                   Y                                          Y                                         N
Name2                   N                                         Y                                         N
Name3                   N                                         N                                        N
Name4                   N                                         Y                                         N
0
Comment
Question by:rav_rav
  • 3
  • 3
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38282041
Can you post a sample file?

In any event, I am thinking that it would be easier to evaluate the source data for the various PTs, or to do this analysis in Access...
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 38282906
I just finished reading the help page called “Consolidate multiple worksheets into one PivotTable report” (use these keywords in help search) — a technique I'm not too familiar with.

I was able to create a single pivot table showing all the names, from three sheets having compatible column headings. I used the “single page field” option, and moved the resulting page field to become the column header. As value, I counted the name field. I obtained something very similar to your request, with a count for Y and blank cells for N. A bit of number formatting can take care of that.

I hope you will find this useful.
(°v°)
0
 

Author Comment

by:rav_rav
ID: 38287629
harfang: thank you for the post; I will try your suggestion and let you know

matthewspatrick: I'm going to try harfang's suggestion first as I'd prefer not to use Access at this point
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:harfang
ID: 38287660
Note: I should be leaving tomorrow for a couple of weeks. If you have difficulties after today, please ask matthewspatrick to help you.

(°v°)
0
 

Author Comment

by:rav_rav
ID: 38287755
harfang,

Tried your suggestion, but had one question.

In the pivot table summary, I need to be able to show which worksheets (source files) that a name exists on.

Specifically, I want to be able to have a quick summary that says that the first data extract had the name but the second didn't.
0
 
LVL 58

Expert Comment

by:harfang
ID: 38288663
The pivot table wizard created a page field, with “Item 1”, “Item 2”, &c for each consolidated range (i.e. each sheet I used). I placed the page field as column header, and renamed the “Item” header labels with meaningful names. This is permanent: the item labels will not revert to their initial values when you requery the pivot.

You should end up with your names as row headers and the names you have entered for your ranges as column headers, and a count of occurrences as value. I hadn't used this in years, but it seemed quite straightforward.

(°v°)
0
 

Author Comment

by:rav_rav
ID: 38308577
harfang:  sorry for the delayed response.  thanks for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question