Excel – Combining multiple Excel 2007 tables into one PivotTable

microsoft excelmicrosoft-excel-2007pivot table

I have two tables, in separate Excel worksheets in the same file, which I'd like to combine. Is this possible?

The tables look like so:

Project | Subproject | Hours
PAlpha  | SPOne      |   5
PAlpha  | SPTwo      |   0.5  
PBeta   | SPThree    |   1
PAlpha  | SPOne      |   1.5


Project | Subproject | Days
PAlpha  | SPOne      |  1
PAlpha  | SPFour     |  0.5
PGamma  | SPFive     |  1.5
PGamma  | SPFive     |  0.5

I can happily get a PivotTable from the first or the second:

Row Labels  | Sum of Hours
[-] PAlpha  |            7
   SPOne    |            5
   SPTwo    |            2
[-] PBeta   |            1
   SPThree  |            1

But what I'd like is to combine the tables, giving something along the lines of:

Row Labels | Sum of Hours | Sum of Days
[-] PAlpha |            7 |         1.5
   SPOne   |            5 |         1
   SPTwo   |            2 |         0
   SPFour  |            0 |         0.5
[-] PBeta  |            1 |         0
   SPThree |            1 |         0
     ...

My best solution so far is to build up a third table listing all the projects and subprojects, and have that collate information from the first two tables. As best as I can work out, though, that requires entering every project and subproject manually into this extra table; I can't come up with a non-macro method of collecting the names of all the projects/subprojects from the two different tables.

I'd prefer a non-macro solution, as I'm comfortable using Excel's native functions, and considerably less comfortable playing with VBA. If macros are the only way to go, though, then they'll have to do…

Update: Following DaveParillo's answer, I've managed to get things to consolidate by project or subproject. I've still not managed both at once, with Excel's pretty collapsing layout to subtotal by project.

Best Answer

There are several ways to solve this, but what I would consider first is a multiple consolidation pivot table. You lose flexibility in manipulating fields, but if you just want the project totals, it's easy.

The next thing I would consider is adding an extra 'key' column to one of your tables. Let's say the 'Hours' table is the one we'll be making the pivot from. Then:

  1. Insert a column A Days in the other sheet, add the formula =B2 & C2. Extend this for every row in your data.
  2. In Cell D2 on the Hours sheet, add the formula =VLOOKUP(A2&B2,Sheet2!A1:Dxxx,4,0), where xxx is the last row in your data on the Days sheet. Extned this formula also.

You now have a soingle consolidated dataset you can make a pivot with.

Related Question