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

T

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

B
  • Question Question
Adding time worked in excel
Replies
3
Views
98
Cubist
Cubist
M
  • Question Question
Populate a table of items based on 1 cell selection
Replies
1
Views
234
Edgar_
Edgar_
datastudent
  • Solved
Combining two columns in one(not concut)
2
Replies
11
Views
220
Fluff
Fluff
L
  • Solved
Sums from Tables based on accumulation of hours
Replies
7
Views
218
LeonardH
L
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
90
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,226,729
Messages
6,192,695
Members
453,747
Latest member
tylerhyatt04

Share this page

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