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

 

[zombie]

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

ok - ive gone here - tried to use the macro - no luck - it fails - im probably doing something wrong

http://www.sendspace.com/file/fo8377

i have uploaded a document with some sample data, the VBA code, and an explaination

thank you for any help and thank you for your time and moneys

cheers
edit: i have just noticed - i may not have had the cells set up correctly - or specified the sum value

 

[zombie]

nuh - arranged the cells as it says in the script - still no go

im looking for summed values of 0 and endless solutions

 

[zombie]

OK,

Ive executed the VBA script from mr TM's website - though its been running for about 45minutes - trying to calculate all the solutions for 1064 rows

Has it failed and excel has shat itself? or does it usually take over an hour to calculate all that ****?

 

[zombie]

should i make another thread for my question?

or continue riding this thread?

 

[decpdp]

Raise how many source numbers there are to the power of 2.
(i) Make a sheet with a list of numbers up to this value in the left hand column starting at row 2.
(ii) Convert to binary text in column B2 downwards with something like "=text(dec2bin(A2),"00000000"). Only use as many zero's as you need for each source number.
(iii) Place the source numbers across row 1 starting at column B1.
(iv) Put the formula "=mid(B$2,1,1)*A$2" in C2.
(v) Copy this formula across to the right hand extent of your list.
(vi) Edit each C3,C4 etc. so that the ,1, in the mid function brackets becomes ,2, etc.
(vii) At the right hand column of this row enter a formula like "=sum(C2:Cn) where n is the last column of the formula in (vi). You might find it helpful to put a copy of the A2 value in the next column of this row.
(vii) Select all your formula cells and copy them down to the last row of your list.
(viii) Save this file
(ix) Now select all the list and copy then paste special values in place.
(x) Use data sort for the list and use the sum column as your index.
(xi) Look down this column for values and combinations which make them up.
(xii) Save this file as a different name.
Good Luck

 

[decpdp]

To make a binary list of text bigger than 512 start by converting to hex (dec2hex) then convert each hex digit individually to binary (hex2bin) then "&" all these text cells together. Just copy this to the binary column of your list.
Hope this helps.

 

[decpdp]

Two more useful columns might be "countif("A2:nn,">0")" where nn is the columns with formulae or just a selective subset of the columns.

 

[crane315]

For future reference:
Find a set of amounts that match a target value
Combination of numbers that sum or match a target value

Hi Tusharm,

It does not work for me...I wonder if what I did wrong. Can you please help me?

 

[nigelandrewfoster]

For future reference:
Find a set of amounts that match a target value
Combination of numbers that sum or match a target value

Excited to find this, but not working for me:

This was my result typing the left-hand column in a contiguous column of figures, 0 for "find all solutions", 15 for "sum to 15" and then my test data??

0 15, 11:12:15, 1, 2, 3, 7
15 15, 11:12:15, 1, 5, 7
2 15, 11:12:15, 2, 4, 7
4 15, 11:12:15, 6, 7
6 11:12:15
8 11:12:15
10
12
3

Thanks for your time.