# creating a reconciliation between two sheets

#### jordanburch

##### Active Member
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

### 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
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.

Replies
3
Views
187
Replies
1
Views
281
Replies
1
Views
276
Replies
0
Views
77
Replies
2
Views
146

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.

### Which adblocker are you using?

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

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