Combining multiple Excel 2007 tables into one PivotTable

Question

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.

Solution

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.

Comments

[me_and]

Ah, something else that I missed out: the tables may have multiple entries, which a PivotTable neatly sorts out, but results in over-counting if you use your key method. That key method also relies on one table's project/subproject list being a superset of the other.

[me_and]

…That's upsetting. Excel 2007 appears to have two entirely different PivotTable wizards, one accessed by the Excel 2003 shorcut Alt+D,P, the other accessed through Insert -> PivotTable. No wonder I couldn't find the consolidated data ranges option…

[me_and]

I've managed to get this working, but it's not ideal: I can't find a way of having it work with Excel's nice expanding/contracting project/subproject layout as in my diagram above. For this, Excel seems to require things to be laid out in a strict single row, single column cross-tab format, so having two columns for categorisation doesn't work.

[DaveParillo]

You would find these two data sets much easier to combine if you could define a single column Duration and convert your days and hours into the same units. Or pick one. Days or hours and use it on both sheets.