Finding a needle in the haystack!

wlambeth

New Member
Joined
Aug 20, 2012
Messages
12
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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]
 
Upvote 0
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.
 
Upvote 0
@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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
I guess I hurt someone's feelings. That was not the intention. Twollaston explained why it should be changed.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top