Reconciling transactions (find which combination equals GL line amount)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I've attached some sample data. What I'm trying to do is figure out which combination of visa transactions make up the GL transaction amount. In the Result(Visa) tab you can see the expected output. I'm thinking I need to build some sort of macro to loop through combinations but was wondering if there's a different/easier way?
Solved! Go to Solution.
- Labels:
- Macros
- Predictive Analysis
- Reporting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Mwoody_05 ,
Can you please provide some more information?
What is the key(s)? You have Company 1 matching to a GL transaction on 2022-10-20 for 34.82. However, that value on that date does not exist but does exist for 2022-11-07.
Can you please explain the required logic?
M.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@mceleavey apologies, no real keys but company does need to match, date doesn't matter.
The Transaction amounts don't match 1:1 so I need to find the correct combination of visa transactions that equal the amount for that company on the GL. You can see in the results tab the answer (Which combination of amounts/company on visa equals the amount for the same company on the GL)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
So you're saying there's no logic? There's no correlation other than randomly adding transactions together to try and find the combinations that add up to the amount in the GL?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Mwoody_05
One way this could be done is via the optimization tool. If we ignore companies for the time being, as this can be done using a batch macro, then the attached example shows how the optimization tool could be used to solve this for one GL transaction at a time. You could loop through each GL transaction again via a batch macro.
You could also do it the other way round, i.e. trying to match one VISA transaction against any number of GL transactions.
Unfortunately many to many on either side is not something my brain wants to think about this late on a Tuesday night.
Of course the process may fail to consolidate in which case the optimization tool may throw a fatal error, again that is not something I'm considering with this single example!
Ben