Comparing Pivot Tables

[MartinS]

OK, so my task is to write a 'tool' that the user can specify the location of two workbooks in a specific format. The first was the 'original' run (1), the second the same but at an 'updated position' (2), so improvements due to data cleansing etc.

Each workbook with have the same tabs, and on two tabs specifically, 6 pivot tables (on each sheet) which summarise the source data. So between (1) and (2) there will be differences. It is almost guaranteed that the tables will differ in size between the 2 workbooks, so i can't just compare cell to cell in each pivot table.

Can anyone suggest some ideas on how to go about comparing pivot tables? Ideally I'd like to load the contents of each pivot table into memory (I can't figure out quite how to do this) and then loop through it and check that it's the same field/item before comparing. The results needs to show 6 tables (for each comparison) showing the differences between (1) and (2), so the final table should contain all the fields and items found in both (1) and (2).

I've had a good think, and have done some googling, but can't get me head around how this can be done. If not working in memory is a better approach, am happy to consider that too.

Thanks in advance

Martin

 

[Johnny C]

Re: Help with Comparing Pivot Tables

It would be a lot easier if you had the source data for each pivot table, do you?

If so, just copy/paste the 'original' data for each tab to a new workbook, and in the next available column just copy/paste 'original' down the data. Then copy/paste the data for the 'Updated' directly below it into each tab and copy/paste 'Updated' to the bottom. Then just create a new pivot table with original/updated as a column and all the columns/rows in the source pivots as rows. it should be easy to spot any differences or do a formula

It's easy enough to write VBA to automate this, create a new workbook, add and name tabs, open each specified workbook, select/copy the source data paste to the new workbook. Then create the pivot tables for each tab and copy formulas down the data.

 

[MartinS]

Re: Help with Comparing Pivot Tables

It would be a lot easier if you had the source data for each pivot table, do you?

If so, just copy/paste the 'original' data for each tab to a new workbook, and in the next available column just copy/paste 'original' down the data. Then copy/paste the data for the 'Updated' directly below it into each tab and copy/paste 'Updated' to the bottom. Then just create a new pivot table with original/updated as a column and all the columns/rows in the source pivots as rows. it should be easy to spot any differences or do a formula

It's easy enough to write VBA to automate this, create a new workbook, add and name tabs, open each specified workbook, select/copy the source data paste to the new workbook. Then create the pivot tables for each tab and copy formulas down the data.

Thanks. I do have the source data, yes. That's a good suggestion, I will take a look at that idea.
Thanks again
Martin

 

[Johnny C]

Re: Help with Comparing Pivot Tables

Goodo, hope it works out. The only 'tricky' past is creating the pivot table. Not because it's difficult, it's not but you need to understand pivot tables in vba and understand the pivottable cache and rename the pivot table as soon as you create it so it's not called PivotTableN where N is some random incremental number you can't predict. If you're not familiar there's plenty of help here.

 

[MartinS]

Re: Help with Comparing Pivot Tables

Goodo, hope it works out. The only 'tricky' past is creating the pivot table. Not because it's difficult, it's not but you need to understand pivot tables in vba and understand the pivottable cache and rename the pivot table as soon as you create it so it's not called PivotTableN where N is some random incremental number you can't predict. If you're not familiar there's plenty of help here.

Thanks