Hi Kevin
The following seemed to work for me
I set up a very simple 2 column table (the number of columns does not
matter)
Column A was headed School, Column B was headed Result
Random Data was entered with a letters for school and some figures for
result.
I then extracted a copy of the rows for School A to Sheet2.
When setting up the PT, I used >
Multiple Consolidation Ranges>Next>Create a Single Page field for
me>Next>Range>select the whole range from Sheet1>Add>Select smaller
range from Sheeet2>Add>Next>New worksheet>Finish
Now, for layout (it all seems counter intuitive, but it works)
Drag Row item to Page area
Drag Page Item to Column area
Drag Column item to Row Area
Drag Value item to Data Area and set type to Average or Sum or whatever
you want.
In the Column Area, Item 1 will be the set of Data for All schools
Item 2 will be the set of Data for school A appearing alongside it
Now from the PT toolbar you need to select Table Options and turn off
Grand Total for Rows and Gran Total for columns as they will be
incorrect as Scholl A's values will be double counted.
With all your columns of "results" appearing down the rows, you will
have you comparison of the Individual school that has been extracted to
sheet 2, with the overall total.
The Row item, which you have dragged to Page area would be left at All,
to pick up all schools. If you did select another school from the Page
dropdown, then just the results for that school would show as a single
column, apart from School A (in this case) where there would be 2
identical columns of data comparing itself with itself.
You could easily automate the extraction of the desired school to
Sheet2, using Advanced Filter.
For more help on this, take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs
Debra also has some downloadable example files with code to automate
procedures, which you should be able to modify to suit your needs.
--
Regards
Roger Govier
"Kevin Labore" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello
>
> I am wondering if there is a easy way to compare 2 pivot tables.
> There have been many times where there is not a way to show a
> comparison of 2 sets of data in one pivot table.
> What I have done in the past for scenario is to have a workbook that
> has 2 worksheets containing the 2 pivot tables.
> I then have a 3rd worksheet that compares the data in the 2 pivot
> tables using GETPIVOTDATA.
> This work fine in most cases however setting up the 3rd sheet can take
> some time.
>
> One example of where I might want to compare 2 pivot tables: I have a
> Pivot Table that shows the Score Test results of an assessment test By
> School, Grade, Subject, Year. I want to show a view of how a
> particular school did in comparison to ALL schools. While you can
> easily compare 2 Schools in a pivot table there is not a way to create
> a view to compare ONE school to ALL schools with the data I am
> comparing. As I mentioned having a separate worksheet reference and
> compare the 2 pivot tables manually was the way to accomplish this.
> Normally the Pivot tables have the same structure and the page Field
> would be SchoolA on one pivot table and "ALL" for the 2nd pivot table.
>
> I have tried searching for info on this but haven't come across of any
> explanation/examples of comparing 2 pivot tables(With a 3rd
> PivotTable)
>
> Thanks in advance for any help
>
> Kevin
>
>
>
Similar Threads
