Macro to find best way to combine numbers to equal a certain sum
Im pretty good at VBA, but this one is going to be a challenge I think. It'll be interesting for sure. I will have multiple rows and columns of numbers.. Something like this:
7.4 , 7.4 , 7.4 , 7.4 , 7.4 , 7.4
3.9 , 3.9 , 3.9 , 3.9 , 3.9
11.9 , 11.9 , 11.9 , 11.9 , 11.9
It will always be the same number in each column. You can assume it starts an A1. I will adjust as needed. But it could be any number of columns or rows.
I want to enter a number in a cell or be asked by an input box or whatever... (lets say the number 12 but it could be anything up to 40) and have a macro find the best combinations of those numbers to come as close to 12 as possible without going over. It can use any of those numbers in any combination or order or as many as needed to come up with as few of combinations as possible.
So in the example above, 1 possibility would be 11 combinations as follows: (best I can figure in my head quickly lol)
Pair 7.4 with 3.9 five times (sum of 11.3 each pair)
11.9 by itself 5 times
Then one 7.4 left over to be by itself.
Please note that numbers in the same row can be put together.. for example 3.9 could be put together 3 times to equal 11.7 but that's not the best way in this case because it would take 12 combinations to complete instead of 11. So the code can look at all these numbers as not being in any order or arrangement whatsoever.
In case it matters, I want to buy as few 12 foot pieces of steel as possible to cut it and get each of the lengths listed the same number of times as the number of columns it's in. (7.4 feet six times, 3.9 feet five times and 11.9 five times)
Unless Excel has some other way of doing this that I don't know about, it's going to be a challenging macro.
Thank you!
Albert
