1
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.
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 – 2010-01-17T11:59:23.820
…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 – 2010-01-17T12:09:22.923
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. – me_and – 2010-01-17T12:28:11.350
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. – DaveParillo – 2010-01-18T15:41:10.380