Reconciling transactions (find which combination equals GL line amount)

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?

fake data.xlsx

Reporting

Macros

Predictive Analysis

Accepted answers

BenMoss

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

Optimization Example.yxmd

All comments

mceleavey

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?

Mwoody_05

@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)

mceleavey

@Mwoody_05 ,

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?

BenMoss

Hi @Mwoody_05

Optimization Example.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703