creating a reconciliation between two sheets

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
282
Office Version
  1. 2016
hey guys,

ive got a project ive been approaching several ways. I need to take a master sheet and a slave sheet and match up transactions, a many to many match. The criteria are filtered on the master and then on the slave and if the dollar amounts match(which they usually do) then you copy and paste the lines from that table that are filtered and then you add it to a new sheet with the same system name, its basically the same sheet with just less data and only the data we need. IE
master
BS limit dp amount
1000 3245 87 100


slave

bs limit dp amount

1000 3245 87 50

1000 3245 87 50

take those two lines. The situation could be reversed as well. one to many many to one or many to many.

I have tried a master key and am not sure if it will work. I have tried copy pasting the data into a new sheet then subtracting the two sums and that seems like the right path but i wanted to reach out to you guys are you are better at this than i am.

Any help is appreciated.

Jordan
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Hi Jordan
your could try putting sumifs() into your slave sheet. then subtract a sumifs that refers to the master sheet, copy all the lines where you get zeros and investigate the ones that don't. so to use your sample in e1 put:
Excel Formula:
sumifs(a:a,a1,b:b,b1,c:c,c1,d:d)  - sumifs(master!a:a,a1,master!b:b,b1,master!c:c,c1,master!d:d)
copy this through column e to the end of your data
this will give you a total on each line of the sum total where column a,b,c are the same across all of the lines less the total from the master where a/b and c match.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,549
Messages
5,625,443
Members
416,106
Latest member
Geo0

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
Top