Comparing Results of Pivot Tables

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
Who is Participating?
harfangConnect With a Mentor Commented:
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.
Patrick MatthewsCommented:
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...
rav_ravAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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

rav_ravAuthor Commented:

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.
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.

rav_ravAuthor Commented:
harfang:  sorry for the delayed response.  thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.