Finding a needle in the haystack!

[wlambeth]

I am trying to reconcile a bank statement that has a single dollar amount and that dollar amount is made up of potentially 100 individual deposits.

My list of single amounts is approx $116k and the amount that cleared is $106k
Is there a macro that will test,in a loop fashion, the sum of amounts until the combination is found that equals the total deposit?

Please let me know if such a macro exists.

Thank you

 

[Twollaston]

I am trying to reconcile a bank statement that has a single dollar amount and that dollar amount is made up of potentially 100 individual deposits.

My list of single amounts is approx $116k and the amount that cleared is $106k
Is there a macro that will test,in a loop fashion, the sum of amounts until the combination is found that equals the total deposit?

Please let me know if such a macro exists.

Thank you

I had this same need at my old job when reconciling deposits vs posted amounts. I did find a way to do it. Unfortunately, it only really works well with low amounts of data. It will still work with more rows of data, it will just take forever and only show you the first successful combination. I was able to do this using the excel solver add in. There is a tutorial on this page. https://www.extendoffice.com/docume...nd-all-combinations-that-equal-given-sum.html
[h=4]
Title:
Find Cells Combination That Equal A Given Sum With Solver Add-In[/h]

 

[Eric W]

In the early days of this forum, they used to have monthly challenges. One of the challenges was to do exactly what you wanted: given a certain amount, find a set of numbers from a list that add up to your amount. In general, this is a tough problem, but there are algorithms to do it. Here's the link with the winning macro:

https://www.mrexcel.com/challenges/accounts-receivable-challenge/

It runs fast, and will show all possible combinations that work. However, if you read the comments, you'll see that with only 54 numbers to check, it came up with over 3000 solutions. So if your list is very large at all, you may get far too many solutions to be useful.

The other downside is that the code is very old, and needs a little cleaning up. I've done that, and gotten it to work, but I've never gotten around to posting the remediated code. I think any decent VBA programmer could do so.

 

[jolivanes]

@wlambeth
I assume that you probably googled for an answer before you posted here. I assume you also know that the better your question is in relation to your problem, the better answers you can expect.
If someone with the same problem you have googled for an answer, do you think he would be so lucky and end up under "Finding a needle in the haystack!"
I can't see the relation at all.

 

[wlambeth]

Thank you so much for your helpful reply Twollaston.
It is all too often some dumb *** will post a snide remark about searching google.
They must make themselves seem smarter than everyone else.
Seriously, like most mothers have told us, "If you don't have something nice to say, keep your mouth shut!"
Great words of wisdom.

Again, thank you for the post.

 

[Twollaston]

I dont think he’s trying to attack you. It’s hard to tell tone in text. I think he’s just saying that if you named your request “Find cell combinations that equal a given sum” then if someone else has the same problem, then they would be able to use your post as a place to find a solution if they google something like that. But it won’t show up in the google search because the thread name isn’t aligned with the goal.

Anyways, I’m happy to help you. I hope you were able to solve your problem with the solutions provided. Have a great weekend

 

[jolivanes]

I guess I hurt someone's feelings. That was not the intention. Twollaston explained why it should be changed.