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?
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?