creating a reconciliation between two sheets

[jordanburch]

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

 

[rondeondo]

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:

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.