Tool to automatically combine many large pivot table in many large Excel sheet?

Question

We have several Excel files that contains large pivot data table with the same structure

For example

File A

Pivot table (Field A, B, C)

File B

Pivot table (Field A, B, C)

We want to combine them into 1 pivot table (A, B, C). Just want to know which ways we can do it ?

• Manual way : open a new empty sheet, copy and paste the pivot there and create the pivot again

• Automatic way : is there some tool that did this ?

Thanks

Answer 1

This is the way I use to go around with this :

• Convert those Excel files (with the same structure pivot) to CSV

• Import to some MSSQL DB

• In my consolidated Excel, I just use MSSQL as an external datasource for it

It's a bit manual but it's the best way I know for now :)

Welcome any other suggestions

Answer 2

I have never found a way to create a new pivot table using more than 1 pivot table as input. If you want to combine, you have to go back to the source data & make a whole new pivot. If the data lives on multiple sheets or is too large to fit on a single sheet, you run into problems.

I have a workbook that I use to work around this problem. If you have macros enabled, it will create a temporary menu bar item (on Excel 2007 it appears under Add-ins) that will allow you to select as many workbooks as you want as input to a single consolidated pivot table. The pivot doesn't have any default layout unless you specify the fields you want in the parts of the setup sheet labeled appropriately. Hopefully it's pretty self explanatory, I've given this away to various people, mostly to help them get around the 65,000 row limit in pre-2007 versions of Excel. I have used this to create a pivot from a combined > 550,000 rows of data. Maybe you can use it.

The thing I like about it is that you can create multiple pivots from the same source data - all the pivots created using the Create Pivot macro share the same pivot cache. Once the first pivot table is created, making more pivot tables results in very small file size increases.

Answer 3

this may come a bit late but what you can do:

create a pivot table linked to an external data source:

1. data source=access file. don't use this wizard, use ms query. there you can create a query that combines several tables as data sources and combines them into one table. you will never see the actual query output in excel, but the pivottable will be based on it.

2. data source=excel file. point the file selector to itself (=the excel file you are using), then you can use all named ranges as tables and do the rest just as you were using access as the data source. for this you will need to put all input data into named ranges, where the first row are the column headers. warning: if you rename the file or saveas different file, the underlying query of your pivot table needs to be redone as the data source is not dynamic. i think that one can also use either pivottables or querytables or both directly as input sources without going via named ranges, but this would probably require vba.