Hi everyone,
I'm new to this forum. I need some help with pivot tables:
I'm trying to consolidate payments Ids together, specifically payments which have the same ID but different payments need to be combines and totalled into one. I done a head-start with pivot tables.
I comparing and combining payments from two different tables. If i do a pivot table from each table (C1 and C2) separately it works but i need to match the ID together and calculate the difference as some payment id will be in both tables, this i don;t know how to do quickly or efficiently. This is small example so ID's in C1 don't appear in C2.
The alternative I tried, was using the data range of both tables but the payments ID from C2 would be appeared to be in different format. It has the - icon by it allowing you to expand or collapse that ID, why is it doing for some payments and not all. I am so confused in getting this to work.
Could some one please help me out.
[TABLE="width: 548"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Payment ID (C1)[/TD]
[TD]Amount (C1)[/TD]
[TD]Payment ID (C2)[/TD]
[TD]Amount (C2)[/TD]
[/TR]
[TR]
[TD="align: right"]6041012759[/TD]
[TD="align: right"]34.3[/TD]
[TD="align: right"]604962185[/TD]
[TD="align: right"]39.165[/TD]
[/TR]
[TR]
[TD="align: right"]6041012764[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604962185[/TD]
[TD="align: right"]239.9925[/TD]
[/TR]
[TR]
[TD="align: right"]6041012764[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604962186[/TD]
[TD="align: right"]44.76[/TD]
[/TR]
[TR]
[TD="align: right"]6041012777[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604962186[/TD]
[TD="align: right"]261.81[/TD]
[/TR]
[TR]
[TD="align: right"]6041012777[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604963066[/TD]
[TD="align: right"]94.5425[/TD]
[/TR]
[TR]
[TD="align: right"]6041012778[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604963066[/TD]
[TD="align: right"]241.7175[/TD]
[/TR]
[TR]
[TD="align: right"]6041012808[/TD]
[TD="align: right"]252.9[/TD]
[TD="align: right"]604963067[/TD]
[TD="align: right"]94.5425[/TD]
[/TR]
[TR]
[TD="align: right"]6041012809[/TD]
[TD="align: right"]258.74[/TD]
[TD="align: right"]604963067[/TD]
[TD="align: right"]196.3575[/TD]
[/TR]
[TR]
[TD="align: right"]6041012876[/TD]
[TD="align: right"]152.89[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]39.165[/TD]
[/TR]
[TR]
[TD="align: right"]6041012877[/TD]
[TD="align: right"]263.56[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]116.3825[/TD]
[/TR]
[TR]
[TD="align: right"]6041012877[/TD]
[TD="align: right"]328.35[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]145.45[/TD]
[/TR]
[TR]
[TD="align: right"]6041012878[/TD]
[TD="align: right"]258.74[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]42.51[/TD]
[/TR]
[TR]
[TD="align: right"]6041012879[/TD]
[TD="align: right"]142.96[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]127.53[/TD]
[/TR]
[TR]
[TD="align: right"]6041012880[/TD]
[TD="align: right"]252.93[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]141.7[/TD]
[/TR]
[TR]
[TD="align: right"]6041012881[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604963683[/TD]
[TD="align: right"]184.21[/TD]
[/TR]
[TR]
[TD="align: right"]6041012881[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604963684[/TD]
[TD="align: right"]14.17[/TD]
[/TR]
[TR]
[TD="align: right"]6041012882[/TD]
[TD="align: right"]73.15[/TD]
[TD="align: right"]604963684[/TD]
[TD="align: right"]69.76[/TD]
[/TR]
</tbody>[/TABLE]
Thank You
I'm new to this forum. I need some help with pivot tables:
I'm trying to consolidate payments Ids together, specifically payments which have the same ID but different payments need to be combines and totalled into one. I done a head-start with pivot tables.
I comparing and combining payments from two different tables. If i do a pivot table from each table (C1 and C2) separately it works but i need to match the ID together and calculate the difference as some payment id will be in both tables, this i don;t know how to do quickly or efficiently. This is small example so ID's in C1 don't appear in C2.
The alternative I tried, was using the data range of both tables but the payments ID from C2 would be appeared to be in different format. It has the - icon by it allowing you to expand or collapse that ID, why is it doing for some payments and not all. I am so confused in getting this to work.
Could some one please help me out.
[TABLE="width: 548"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Payment ID (C1)[/TD]
[TD]Amount (C1)[/TD]
[TD]Payment ID (C2)[/TD]
[TD]Amount (C2)[/TD]
[/TR]
[TR]
[TD="align: right"]6041012759[/TD]
[TD="align: right"]34.3[/TD]
[TD="align: right"]604962185[/TD]
[TD="align: right"]39.165[/TD]
[/TR]
[TR]
[TD="align: right"]6041012764[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604962185[/TD]
[TD="align: right"]239.9925[/TD]
[/TR]
[TR]
[TD="align: right"]6041012764[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604962186[/TD]
[TD="align: right"]44.76[/TD]
[/TR]
[TR]
[TD="align: right"]6041012777[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604962186[/TD]
[TD="align: right"]261.81[/TD]
[/TR]
[TR]
[TD="align: right"]6041012777[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604963066[/TD]
[TD="align: right"]94.5425[/TD]
[/TR]
[TR]
[TD="align: right"]6041012778[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604963066[/TD]
[TD="align: right"]241.7175[/TD]
[/TR]
[TR]
[TD="align: right"]6041012808[/TD]
[TD="align: right"]252.9[/TD]
[TD="align: right"]604963067[/TD]
[TD="align: right"]94.5425[/TD]
[/TR]
[TR]
[TD="align: right"]6041012809[/TD]
[TD="align: right"]258.74[/TD]
[TD="align: right"]604963067[/TD]
[TD="align: right"]196.3575[/TD]
[/TR]
[TR]
[TD="align: right"]6041012876[/TD]
[TD="align: right"]152.89[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]39.165[/TD]
[/TR]
[TR]
[TD="align: right"]6041012877[/TD]
[TD="align: right"]263.56[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]116.3825[/TD]
[/TR]
[TR]
[TD="align: right"]6041012877[/TD]
[TD="align: right"]328.35[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]145.45[/TD]
[/TR]
[TR]
[TD="align: right"]6041012878[/TD]
[TD="align: right"]258.74[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]42.51[/TD]
[/TR]
[TR]
[TD="align: right"]6041012879[/TD]
[TD="align: right"]142.96[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]127.53[/TD]
[/TR]
[TR]
[TD="align: right"]6041012880[/TD]
[TD="align: right"]252.93[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]141.7[/TD]
[/TR]
[TR]
[TD="align: right"]6041012881[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604963683[/TD]
[TD="align: right"]184.21[/TD]
[/TR]
[TR]
[TD="align: right"]6041012881[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604963684[/TD]
[TD="align: right"]14.17[/TD]
[/TR]
[TR]
[TD="align: right"]6041012882[/TD]
[TD="align: right"]73.15[/TD]
[TD="align: right"]604963684[/TD]
[TD="align: right"]69.76[/TD]
[/TR]
</tbody>[/TABLE]
Thank You