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.