Pivot tables - Consildating payments into one

2

24parmar2

New Member
Joined
Feb 15, 2019
Messages
5
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
is that what you want?
I added some rows from C1 to C2 with different values

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Payment ID (C1)[/td][td=bgcolor:#5B9BD5]Amount (C1)[/td][td][/td][td=bgcolor:#5B9BD5]Payment ID (C2)[/td][td=bgcolor:#5B9BD5]Amount (C2)[/td][td][/td][td=bgcolor:#70AD47]Payment ID (C1)[/td][td=bgcolor:#70AD47]Payment ID (C2)[/td][td=bgcolor:#70AD47]Subtraction[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012759​
[/td][td=bgcolor:#DDEBF7]
34.30​
[/td][td][/td][td=bgcolor:#DDEBF7]
604962185​
[/td][td=bgcolor:#DDEBF7]
39.17​
[/td][td][/td][td=bgcolor:#E2EFDA]6041012759[/td][td=bgcolor:#E2EFDA]6041012759[/td][td=bgcolor:#E2EFDA]
-142.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012764​
[/td][td]
146.30​
[/td][td][/td][td]
604962185​
[/td][td]
239.99​
[/td][td][/td][td]6041012764[/td][td]6041012764[/td][td]
-8457.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012764​
[/td][td=bgcolor:#DDEBF7]
146.30​
[/td][td][/td][td=bgcolor:#DDEBF7]
604962186​
[/td][td=bgcolor:#DDEBF7]
44.76​
[/td][td][/td][td=bgcolor:#E2EFDA]6041012777[/td][td=bgcolor:#E2EFDA]6041012777[/td][td=bgcolor:#E2EFDA]
-1765.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012777​
[/td][td]
253.15​
[/td][td][/td][td]
604962186​
[/td][td]
261.81​
[/td][td][/td][td]6041012879[/td][td]6041012879[/td][td]
-47.04​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012777​
[/td][td=bgcolor:#DDEBF7]
253.15​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963066​
[/td][td=bgcolor:#DDEBF7]
94.54​
[/td][td][/td][td=bgcolor:#E2EFDA]6041012880[/td][td=bgcolor:#E2EFDA]6041012880[/td][td=bgcolor:#E2EFDA]
-3587.07​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012778​
[/td][td]
253.15​
[/td][td][/td][td]
604963066​
[/td][td]
241.72​
[/td][td][/td][td]6041012881[/td][td]6041012881[/td][td]
-2450.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012808​
[/td][td=bgcolor:#DDEBF7]
252.90​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963067​
[/td][td=bgcolor:#DDEBF7]
94.54​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012809​
[/td][td]
258.74​
[/td][td][/td][td]
604963067​
[/td][td]
196.36​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012876​
[/td][td=bgcolor:#DDEBF7]
152.89​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963068​
[/td][td=bgcolor:#DDEBF7]
39.17​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012877​
[/td][td]
263.56​
[/td][td][/td][td]
604963068​
[/td][td]
116.38​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012877​
[/td][td=bgcolor:#DDEBF7]
328.35​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963068​
[/td][td=bgcolor:#DDEBF7]
145.45​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012878​
[/td][td]
258.74​
[/td][td][/td][td]
604963070​
[/td][td]
42.51​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012879​
[/td][td=bgcolor:#DDEBF7]
142.96​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963070​
[/td][td=bgcolor:#DDEBF7]
127.53​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012880​
[/td][td]
252.93​
[/td][td][/td][td]
604963070​
[/td][td]
141.70​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012881​
[/td][td=bgcolor:#DDEBF7]
146.30​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963683​
[/td][td=bgcolor:#DDEBF7]
184.21​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012881​
[/td][td]
146.30​
[/td][td][/td][td]
604963684​
[/td][td]
14.17​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
6041012882​
[/td][td=bgcolor:#DDEBF7]
73.15​
[/td][td][/td][td=bgcolor:#DDEBF7]
604963684​
[/td][td=bgcolor:#DDEBF7]
69.76​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
6041012879​
[/td][td]
190.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]
6041012880​
[/td][td=bgcolor:#DDEBF7]
3840.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
6041012881​
[/td][td]
2743.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]
6041012759​
[/td][td=bgcolor:#DDEBF7]
177.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
6041012764​
[/td][td]
3807.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]
6041012764​
[/td][td=bgcolor:#DDEBF7]
4943.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]
6041012777​
[/td][td]
2272.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
result is with matched IDs only
 
Upvote 0
Hello,
Yes this is great. How did you match the two IDs together. As shown in the green table please. Thats the main part i'm stuck on with the pivot table.
Unless i'm missing something very simple.
 
Upvote 0
What is your Excel version?
If 2010/2013 - have you installed PowerQuery add-in?
 
Upvote 0
Office 16, what is power query does it do what you done for that green table. As i have an example with over 11k records. Some Id's match both tables. Ones which do, I need to do a comparison for different in amounts between those two.
 
Upvote 0
or if you want PivotTable

Alt+D+P

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Row[/td][td=bgcolor:#DDEBF7]Amount (C1)[/td][td=bgcolor:#DDEBF7]Amount (C2)[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604962185​
[/td][td][/td][td]
279.1575​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604962186​
[/td][td][/td][td]
306.57​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604963066​
[/td][td][/td][td]
336.26​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604963067​
[/td][td][/td][td]
290.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604963068​
[/td][td][/td][td]
300.9975​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604963070​
[/td][td][/td][td]
311.74​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604963683​
[/td][td][/td][td]
184.21​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
604963684​
[/td][td][/td][td]
83.93​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012759​
[/td][td]
34.3​
[/td][td]
177​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012764​
[/td][td]
292.6​
[/td][td]
8750​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012777​
[/td][td]
506.3​
[/td][td]
2272​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012778​
[/td][td]
253.15​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012808​
[/td][td]
252.9​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012809​
[/td][td]
258.74​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012876​
[/td][td]
152.89​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012877​
[/td][td]
591.91​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012878​
[/td][td]
258.74​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012879​
[/td][td]
142.96​
[/td][td]
190​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012880​
[/td][td]
252.93​
[/td][td]
3840​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012881​
[/td][td]
292.6​
[/td][td]
2743​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6041012882​
[/td][td]
73.15​
[/td][td][/td][/tr]
[/table]
 
Upvote 0
So i've consolidated the replicated payments ID;s in two seperate table for table 1 and table 2. I need to match ID's which consist in both tables that i have no idea.
 
Upvote 0
did you read anything from links I gave you?

load both tables into PowerQuery via ribbon: Data - From Table
 
Upvote 0
Yes the but the two links don't give an explanation of how you got there. I've been playing around with Get and Transform and have gotten no luck. How did you achieve those results.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

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