Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Are you looking for a 'click around in excel solution' or is a macro solution OK? –  DaveParillo Nov 12 '09 at 5:12
add comment

3 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.