MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
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.