Combine two excel tables with one-to-many relationship into pivot table

[totalchaos]

I have data tables into two separate worksheets as follows:

Sheet1 table: Unique values in column A
[TABLE="width: 144"]
<colgroup><col style="width: 48pt;" span="3" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Excel 2007[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: #E0E0F0"] [/TD]
[TD="class: xl64, width: 64, bgcolor: #E0E0F0"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: #E0E0F0"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: #E0E0F0"]2[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Cost Code[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Budget[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: #E0E0F0"]3[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: #E0E0F0"]4[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: #E0E0F0"]5[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: #E0E0F0"]6[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Sheet1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 table: data with multiple cost codes

[TABLE="width: 192"]
<colgroup><col style="width: 48pt;" span="4" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Excel 2007[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"] [/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]A[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]B[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]C[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Employee[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Cost Code[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Actual[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]3[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]4[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]5[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Sheet2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


The first table has a one-to-many relationship with the second table. That is, one cost code on table1 to multiple cost codes on table2. I want to combine the two tables into one pivot table with the results on a third sheet as such:

[TABLE="width: 192"]
<colgroup><col style="width: 48pt;" span="4" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Excel 2007[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"] [/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]A[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]B[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]C[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Cost Code[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Budget[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Actual[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]3[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]4[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]5[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Sheet3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Can anyone recommend a solution to this?

 

[AhoyNC]

see attached link.

http://www.youtube.com/watch?v=mR8eOS7GFAw&feature=plcp

 

[totalchaos]

Thanks for the link. The video explains consolidation which can only be done if each worksheet has the same structure. If I create a pivot table on table 2 only, I will get only cost codes with actual hours. I want to be able to combine the tables so that cost code C shows in the results even though there are no actual hours. I hope there is a work around if this is not possible.

 

[AhoyNC]

I think I got what you wanted using the consolidation.

In table one I took cost code & Budget then in table two I only took the cost code & Actual columns (don't take the employee column).

Excel Workbook
	A	B	C	D
2
3	Sum of Value	Column
4	Row	Actual	Budget
5	A	3	10
6	B	13	20
7	C		30
8	D	10	40
9
Sheet

 

[totalchaos]

I tried the same thing and got the same result. Thanks. The next question is how can I do the same thing if the columns in the data table 2 are not aligned next to each other. Is there a way to select non-contiguous ranges in the reference box of the consolidation dialog window.