find a combination of numbers that equal a given sum

[mcgerks]

Hi,

If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?

Thanks for the help

 

[Gerald Higgins]

Hi mcgerks.

How can you possibly achieve this ?
You could arrive at 13 in at least two ways . . .
9+3+1
7+5+1

 

[Fazza]

The solver can do this sort of stuff. Suggest you google for examples - I have seen something like you're after but unfortunately I can't locate it now.

 

[mcgerks]

Thanks Fazza the solver add-in did the trick!

-Keith

 

[tusharm]

For future reference:
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/templates/match_values/index.html

 

[Peter Compton]

This is a question often asked by people trying to do bank reconciliations. There is a way of doing it without using VBA, which is not 100% reliable, but may help. Fisrt of all, make sure you have the Solver Add-in installed. What you then need to do is to line up all your source numbers in, say, column A. Then, in column B, put zeroes in every cell next to the values in column A. At the bottom of your column of zeroes (let's say cell B11), put the formula =SUMPRODUCT($A$1:$A$10,$B:$B$10) (I'm just assuming there are only 10 values, of course there could be more).
Now in the Solver, in the first dialogue box, enter $b$10 as your Set Target Cell and make sure you have put the value you need in the place for Equal to: Value of:
In the By Changing Cells box, enter $B$1:$B:$10
Now click on Add next to where it says Subject to the Constraints and enter that $b$1:$b$10 = binary, then OK
Now click on Options and tick Assume Linear Model, then OK
Then click Solve

If there is a correct answer, the relevant cells in $b$1:$b$10 will become 1 instead of 0, and you will have your answer. This usually works, but is subject to the following constraints. Firstly, it is better with short-ish lists of numbers, and secondly, if there is more than one solution, it will only give you the first one it finds.

 

[purdyk1]

This is a great formula and thanks, Peter, for posting it. However, there are a few typos in the post and I thought I'd clarify for other users.

The first formula should read =SUMPRODUCT($A$1:$A$10,$B$1:$B$10). In the Solver, the Set Target Cell should be $B$11 and the 'By Changing Cells' box should contain $B$1:$B$10.

 

[tusharm]

The page I referred to includes the Solver approach including a downloadable template.

This is a great formula and thanks, Peter, for posting it. However, there are a few typos in the post and I thought I'd clarify for other users.

The first formula should read =SUMPRODUCT($A$1:$A$10,$B$1:$B$10). In the Solver, the Set Target Cell should be $B$11 and the 'By Changing Cells' box should contain $B$1:$B$10.

 

[zombie]

hey guys

this is what i need to do

but with a list of 1000+ records
solver = fail

i havent tried cutting it down into sections
because some numbers could be across sections :/

anyone know of a way to punch out 1K+ records?

find the numbers that sum to zero?

 

[zombie]

I checked the MT website - some nice advice - though im too much of a noob to build macros - so more research for me