How to find combination of numbers to match balance?

I have 45 input cash amounts, I need to find out if any combination of these sums to a balance

It could be just two input numbers, or it could be 35 input numbers

Example:
My input numbers are...
-1,860
1,860
48
1,300
90
50,000
-23,624
24,779
-24,779
-3,722
-3,338
-880
-152
1,561
2,404
3,338
3,722
4,957
-305,718
-41,307
-41,307
-30,980
-10,326
-6,196
-2,915
-2,571
-2,065
-87
-52
-17
17
52
87
2,065
2,571
6,196
10,326
30,980
41,307
41,307
100,503
110,168
305,718
-144
144


I need to find out if some of these can be summed to give a Balance of -209,000



I have many hundreds of these to check, with variable numbers of inputs


I can't see how to write a macro do check these


Any ideas?

Thanks in advance for any suggestions

You could use Solver to do this.

Put your numbers in A1:A45
In C1 put in =A1*B1 and drag down to C45, Make C46 =SUM(C1:C45)
Then use solver for cell C46 = -209000 with the constraint that B1:B45 is binary

Not my idea. I saw it somewhere a while ago.

(see attached)

There is a problem with using Solver for this kind of problem as Solver only will find one solution where there could (probably) be quite a number of these.

Tushar-Menta did set up a vba solution and a forum member JohnTopely added a more user friendly interface. I've uploaded a sample file where I set result to 10 different
combinations and there are many more solutions.

On his Excel page there are an add-in that finds combinations that add up to a specific number but it's not a freebie. Link
http://www.tushar-mehta.com/excel/so...rget/index.htm

Alf

Originally Posted by Alf

There is a problem with using Solver for this kind of problem as Solver only will find one solution where there could (probably) be quite a number of these.
Alf

How do you know it's a "problem"? The OP may only require one solution. He certainly hasn't indicated otherwise.

Do we know if the OP is aware of the multiple solution that can be found?

What...do you think that he might believe that there is only one unique solution for every set of numbers?

Here is a thought.

Here are 4 numbers, 4,6,20,10. Do you think he might believe there is only one solution to make a total of 30?

In the examples given by the OP there are several pairs of numbers which are +ve and -ve, and thus cancel each other out in any sum. So, if there is a solution to finding a Balance which adds to -209.00, then there are bound to be other solutions which include these pairs in different combinations.

We need more information from the OP.

Pete

I just need to know if any solution exists, I don't care if there are many possible solutions

Also, each number can only be used once. Solver comes up with solutions like 2800 * "first number" - 6389 * "second number"...etc

Originally Posted by Ted888888

I just need to know if any solution exists, I don't care if there are many possible solutions

Also, each number can only be used once. Solver comes up with solutions like 2800 * "first number" - 6389 * "second number"...etc

That is precisely why I said "...with the constraint that B1:B45 is binary". Did you download my attachment in post #2?

Thank you! Yes, I've tried your Solver solution, that does not seem to work as I can't specify the constraint that B1:B45 is binary, perhaps its my lack of knowledge?

The addin TM Match works though, it finds a solution in under a second

Yes, the number of combinations is huge!

This addin solved it in under a second though! TM Match Target - the forum won't allow me to post the link

See solver setting i.e. click button marked "Options". Check if box marked "Ignore Integer Constraint" is ticked. If so untick it and test again.

Alf