automatically compare the bank book & accounts book

[sryair]

Hi,

In my workbook I have 2 sheets- Bank Statement and Bank Book.
So, I want a code which matches the entries in both sheets and would bring a unique number(say 0001) in "matching reference" column of the respective rows in both the sheets.

Now the matching of entries should be on various combinations:
"Date+Description+Amount" should match and bring the unique number in the respective entries in both the sheets else match
"Description+Amount" and bring the unique code.


Thanks in advance!

 

[sryair]

The match should be on several levels:

1. When there is a full match of Date + Description + Amount
2. Partial match (the date in the bank books a few days after the invoice let's say 4 days)
3. Partial match ((the description is different between the invoice and the bank, let's say the invoice for XXX and the bank shows YYY)
4. Partial match (the amount rounded in the invoice is 99.9 and in Bank 100)

When there is no full match of all the parameters I expect to get the best weighted match i.e. the closest amount to the amount on the invoice, the closest date to the invoice and the closest description to the invoice.