consolidate using pivot table

[lezawang]

Hi
I have excel 2013. I want to consolidate date from 3 different sheets using pivot table not data-->consolidate tool. so i created these 3 tables and insert Table1, Table2, and Table3.
I was inside Table3, I clicked insert Pivot and checked Allow Data Model.
In pivot table, I created Relationship Table 3 -->item, table1-->item
and Table3-->item, table2-->item

I expanded Table one and move Item to Rows
and Jan to Values

Then opened Table 2 and moved Jan to Values
but excel is asking me to Create Relationship which I already did. Alos
How can I added all these values and create table like below using pivot table?
Thank you so much



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD]mar[/TD]
[/TR]
[TR]
[TD]item1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]item2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD]mar[/TD]
[/TR]
[TR]
[TD]item1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]item2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD]mar[/TD]
[/TR]
[TR]
[TD]item1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]item2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


I want

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD]mar[/TD]
[/TR]
[TR]
[TD]item1[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]item2[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

The file is here:

https://drive.google.com/file/d/12mX4V7VpNkC2m-CMyrhIKvy4Y0wk6BAg/view?usp=sharing

 

[sandy666]

maybe try Alt+D+P

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#4F81BD]Sum of Value[/td][td=bgcolor:#4F81BD]Column Labels[/td][td=bgcolor:#4F81BD][/td][td=bgcolor:#4F81BD][/td][td=bgcolor:#4F81BD][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#4F81BD]Row Labels[/td][td=bgcolor:#4F81BD]jan[/td][td=bgcolor:#4F81BD]feb[/td][td=bgcolor:#4F81BD]mar[/td][td=bgcolor:#4F81BD]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]item1[/td][td]

6​

[/td][td]

6​

[/td][td]

6​

[/td][td]

18​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]item2[/td][td]

6​

[/td][td]

6​

[/td][td]

6​

[/td][td]

18​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]item3[/td][td]

6​

[/td][td]

6​

[/td][td]

6​

[/td][td]

18​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]item4[/td][td]

6​

[/td][td]

6​

[/td][td]

6​

[/td][td]

18​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Grand Total[/td][td]

24​

[/td][td]

24​

[/td][td]

24​

[/td][td]

72​

[/td][/tr]
[/table]