Permutations problem

Range A1:A20 ("Source") contains a column of constant numerical values. If necessary or helpful I can arrange that it be sorted by (eg) value.
Cell Z1 ("Target") contains a single constant numerical value.
It is a certainty that there is at least one combination of cells in "Source" the sum of which equals the value of "Target".
I wish to populate range B1:B20 ("Solution") with the corresponding entries in "Source" the total of which adds up to "Target".
I don't need all possible solutions. Any one will do (Eventually if I solve this problem I may look to find a method of cycling through alternative solutions but that sounds difficult).

I think that this is likely to be a wheel already invented, so I throw myself on your kindness. Am happy to use VBA if appropriate.

Thanks

Eh I was able to get it with Solver, but kind of a hacky solution.

A1:A20 = source range
Z1 = target

B1:B20 = zeroes or ones
//A boolean TRUE/FALSE, one is include and zero is do not include

Z2 = (SUMPRODUCT(A1:A20, B1:B20) -Z1) ^2

Then run Solver where:
--Set Objective = Z2
--To: Value of: 0
// (you can also use "Min" but in my very limited testing that took longer to iterate, I'm not sure it matters)
--By changing variable cells:
B1:B20
--Subject to Constraints:
B1:B20 <=1
B1:B20 >= 0
B1:B20 = integer

Then hit solve and it will return whatever solution is most local to wherever the algorithm happens to go.

If Solver says "no solution" then just try fiddling with the default B1:B20 values to get that Z2 value a little closer, it doesn't have to be exact, you just need to get it into the same local area.

Note that this will find a solution, and it will be whichever solution happens to be "closest" to your initial guesses for having 1s in B1:B20.

Finding all solutions, like you expect, is a pretty serious order; I'd have to dig up some books, and getting an exhaustive answer is going to require significantly more rigor than the half-hearted Least Squares I've slapped together here.

You're probably going to take a performance hit moving the SUMPRODUCT out of a single cell and into the C1:C20 range (well, that probably won't matter too much though), and doing just the difference instead of the difference^2 is kind of odd, but yes, that would work.

Hi 1eyedjack,

These are fun problems that show the power of Excel and VBA. Find the attached where you can type in how many numbers to pick and the sum you want it to equal. Then click the button and random picks will happen until an answer is reached. You can change the numbers in A1 to A20 to match your problem. See the code and attached.

Please Login or Register  to view this content.

VBA Random Pick to sum to Total.xlsm