VBA/Macros for Reconciliation/matching values between two workbook

[xpost][/xpost]

Dear Members,

Hope you are doing well.

Its part of the job to reconcile a large list of transactions from the provided two files:

1. Break report

2. system report

I believe instead of manual reconciling this activity can be done using macros. the idea is to tentatively match the fee payments expected against the payments received in the break report. If we identify a potential match, highlight the fee or row so we can mark it"received" in our system and process the entry.

in the system report we have:

- column R,S,T: the expected payments (depending on currency)
- columns A the invoice ref
- column E and F , client name and bank name

in the break report, we have:

- Column E , we limit our scope to "L CR" for payment received.
- Column F,G :currency and amount
- column I , ref comments where we can identify name and reference if lucky the invoice # provided

The tricky part here is to deal with the round amount (examples 5,000.00 USD; 10,000.00 HKD). If we spot a potential match on break report based on invoice # (starts with CTLA/CLTA) or currency and amount, we need to identify the client name matches (full or partial) or if the comment contains part of the deal name of the invoice #.

We do not have a unique identifier such as account etc.

The target is to:

1.Get these 2 reports(system report & break report) generated and downloaded from systems.

2.Run the built-in Macro to propose matching.

3. Operator to review the proposed matching.

If at least 60-70% of data matches it would be very helpful.

Please see attached file for reference which has minimal data (the actual file can have upto 1000 rows) and thank you so much.

Break report.xlsxsystem report.xlsx

*** Updated excel sheet and comments***

I now have attached samples highlighting matching rows and comments under column U with 'match' ,'Partial match' OR 'no match' in "system report" excel sheet. Below is detailed description of match,partial match and no match values:

1. Row2 of system report(Not Highlighted): No match as no details of this row exist in break report extract.

2. Row3 of system report (Highlighted in Green ) Match, based on INV # located under cell I2 ,amount under cell G2 and client name under cell A4 on break report.

3. Row4 of system report (Highlighted in Blue) Partial Match,based on client name under cell A3 as Apple INC vs Apple in system report ,amount under cell G3 and partial invoice # under cell I3 of break report.

4. Row4 of system report (Highlighted in Light Red ) Match, based on client name under cell A4 and amount under cell G4 of break report.

5. Row5 of break report (Not Highlighted): No match as no details of this row exist in system report extract.(vice-versa).

Hope the above is not too confusing.

Also ,if this is way too complex we can look at , match with combinations and permitation of two criteriaBreak report.xlsxsystem report.xlsx such as :

1) Last 5 digits of invoice number in system report (column A) and client name or amount.

OR

2) With Partial client name and Amount.

OR

3)Complete Invoice number and amount.

OR

4)With just partial match with client name.

Similar post is updated on : https://www.excelforum.com/exc…g-values.html#post5524293