Remove matching rows with pause function

[DK4700]

I have a user asking for help to make the following job more automatic. I have tried both via Power Query and simple macros, but I can't seem to find a solution making sense.

He has two lists containing the company payments - one is exported from the bank, the other one is exported from Economics. In theory these lists should match 1:1- his job is to find discrepancies. Both lists contain an amount and a date to steer from - but the dates are only 90 % similar in between the two datasets... payment date (from bank file) / transaction date (from economics file) are not always the same but the order of transactions usually is.

There is no key to match data 1:1.

Today he copies the two data set to the same sheet. Bank data in A:H and Economics data in L:AB. Then he sorts each list by date and then amount.
Then creates formula in column J ($E1-$Z1) to subtract the amounts in same row - if this results in 0 - he assumes it's a match between the two lists.

Best case will be a template, where he manually will copy in the two datasets. And the following are then automatised:

• Sorting data set 1 by column A, then E,
• Sorting data set 2 by column N, then Z.
• Now rows are removed from the top if $E1-$Z1=0, stopping at the first row <> 0.

Then he can do some changes in the file (e.g. manually delete lines, and then restart to automatically delete the rows where $E1-$Z1 = 0 until next time an <>0 might occur.​

Does this make sense at all in simple text? If so, any thoughts/solutions how to help?

Thanks,

 

[alansidman]

It will be better if you supplied a sample worksheet of 8-12 records using XL2BB and then mock up what your expected results for the sample will look like. No pictures as we cannot manipulate data in a picture.