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

totalchaos

New Member
Joined
Jul 6, 2012
Messages
40
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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
ABCD
2
3Sum of ValueColumn
4RowActualBudget
5A310
6B1320
7C30
8D1040
9
Sheet
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top