Compare 2 Pivot Tables

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



0
kevinl (11)
3/16/2007 11:40:16 PM
excel 39879 articles. 2 followers. Follow

2 Replies
684 Views

Similar Articles

[PageSpeed] 23

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" <kevinl@somewhere.net> wrote in message 
news:gvadncNEKZ1Mt2bYnZ2dnUVZ_o2vnZ2d@comcast.com...
> 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
>
>
> 


0
roger5293 (1125)
3/17/2007 11:59:12 AM
HI Roger

Thanks for the explanation
I figured there was simpler solution than my alternate solution which did 
work, but was work to set up the 3rd sheet and do the referencing and 
formatting

Kevin


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:%23xGzGvIaHHA.3584@TK2MSFTNGP02.phx.gbl...
> 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" <kevinl@somewhere.net> wrote in message 
> news:gvadncNEKZ1Mt2bYnZ2dnUVZ_o2vnZ2d@comcast.com...
>> 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
>>
>>
>>
>
> 


0
kevinl (11)
3/18/2007 12:10:45 AM
Reply:

Similar Artilces: