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

Excel 2007
	A	B
2	Cost Code	Budget
3	A	10
4	B	20
5	C	30
6	D	40
Sheet1

<colgroup><col style="width: 48pt;" span="3" width="64"> <tbody>
</tbody>

Sheet 2 table: data with multiple cost codes

Excel 2007
	A	B	C
2	Employee	Cost Code	Actual
3	1	A	3
4	1	B	5
5	2	B	8
6	2	D	10
Sheet2

<colgroup><col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

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:

Excel 2007
	A	B	C
1	Cost Code	Budget	Actual
2	A	10	3
3	B	20	13
4	C	30	0
5	D	40	10
Sheet3

<colgroup><col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

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.