Search combination of numbers for a specific total

Is there a way to enter a number in say cell a1 then have excel query through a set of several other numbers column b in this case to find the combination of the numbers in column b that totals out to the number I have entered into cell a1?
Who is Participating?
Nico BontenbalCommented:
In this question:
I wrote some code to do this. I changed it to apply for your situation (decimals and negative values). But there are just too many combination. When I try it for the first 20 values I get a result in about 15 seconds. But for the first 30 values I stopped the code after 15 minutes. So 600 values will take way to long. Maybe it is possible to optimize the code a bit, but hopefully it is possible to limit the number of options like robhenson suggests.
You'll find my code in the attached file. Enter a number in Cell A1 and it will pop up with a msgbox with the possible combinations (only for values from the first 20 rows).

Rob HensonFinance AnalystCommented:
This is like the age old Hitch Hikers Guide to the Galaxy question. The answer is 42 but what is the question?

Depending on the size of the data, there could potentially be so many different combinations that the question is nigh on impossible to answer.

How many numbers to be selected? What if one of the numbers is equal to the answer? Will numbers be negative?

Apologies if not helpful
Rob H
wcodyAuthor Commented:
All the numbers are listed in column b, and the out come I am looking for is in cell a
Rob HensonFinance AnalystCommented:
Apologies if you misunderstood my comment.

I understood the question, the numbers are in column b and you need to know which add up to the value in a1.

Your example shows over 500 numbers in column b, with positive and negative numbers.

I assume you are wanting to find out which transactions make up the balance which you are entering in a1. You could start by shrinking the data by creating a pivot table based on id_num. This will enable you to strip out the id_num with zero balance.

Rob H
Arno KosterCommented:
you could try to use the solver on this equation :

say A2 contains the "starting row number"
say A3 contains the "ending row number"
say A4 contains the formula =SUM(INDIRECT("B" & A2 & ":B" & A3))

you can then start from an educated guess and use the solver or a vb script to try out values for which the totals from B add up to the value you want.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.