Find all possible combinations of numbers that equals a sum with criteria of maximum numbers to sum and sum range

[Hussaan]

Hello,
I want to create all possible combinations of numbers (for example 45,90,50,30,..)from a define list with and without repetition whose sum is between a define criteria. (say 150 to 180) with criteria that use maximum five numbers with and without repetitive.
Like
90 + 90 = 180
90 + 45 + 45 = 180
45 + 45 + 45 + 45 = 180
....
I have an excel file. In which I am doing all things manually which is very time consuming and also error chances. Kindly help me for a excel vba code that can help. I am new to vba so don't know how to do.

In the current file following are the inputs.
Number to sum in column range A3:A200.
Cell D2 is the input of maximum sum value required.
Cell D3 is the input of minimum sum value required.
Cell D4 is the input of maximum number that can be used to find sum
Output
C8:L1000 or more than 1000 depending on the output.
I have attached the file.
Thanks

 

[Marc L]

Hello,​

​

many issues :​

​

except I can't open your attachment on my test computer (I have to grab later another computer with a more recent Excel version)​

it's first not an Excel / VBA issue but a mathematical problem where in the initial post​

you should explain at least what is the algorithm you use to resolve your need …​

​

I already saw - like I yet have posted solutions - on web only for an obvious without any repetition way for accountancy / finance purpose.​

​

Another issue is why Excel as it's one of the slowest application (via VBA) and it depends too on the algorithm used.​

The more numbers in the source list, the slower (and could be worse with repetitions) …​

So it could last few seconds for the shortest lists to tens of minutes even an hour for the longest lists and thus just for one value to sum !​

​

 

[Hussaan]

Thanks for the reply. I have attached excel file that can be opened in old version.
I want to cut short lengths (say 45,90,50,30... ) inch (customer requirement) from a big lengths (from 150 to 182) inch. I want to find all possible combinations (with and without repetition) from customers required lengths that sum 150 to 182.

 

[Marc L]

Thanks for your new attachment where the source list has only 12 numbers but in the real world, how many ?​

What is your algorithm ?​

​

 

[Hussaan]

Sorry, i am not able to explain well.
The numbers can be upto 200.
Objective is to find all possible combinations using maximum 10 numbers start from minimum number.
For example desire number is 150 to 182 so vba code will look all possible combinations from inputs column A3:A200 and make combinations. Conditions is use maximum 10 number and sum should be 150 to 182.

 

[Marc L]

So what is your way when you manually operate, what is your logic to not forget any combination ?​

​

 

[Hussaan]

No particular logic, no way to check due to manual entry. I just only check all numbers are used and sum is meeting criteria. Due to this reason I am looking for a excel vba code to help.

 

[Marc L]

As any code must follow an accurate logic, so without any, no code is possible …​

And how will you check if any solution is correct ?!​

​

According to your attachment, from 11 numbers (without duplicate) to get a sum between 150 & 182 with a maximum of 10 numbers​

whatever with duplicates or not, have you an idea of the number of combinations ?​

​

 

[Marc L]

The answer is 393 combinations just for your specific list of 11 numbers, so imagine for 200 !​

My 'simulator' works fast with your attachment but it is fixed for combinations of 8 numbers max …​

So in order to get more numbers in combinations I have to reorganize it to an 'universal' way,​

I have to search in my old archives which path I have followed for a financial similar case.​

​

But on your side you may perform a web search and find out what you expect for or share an algorithm …​

​

 

[Hussaan]

Ok i will perform web search for that.
If you have some thing that can work for maximum 8 number and entery number less than 200. It will be a great help.
Thanks for helping.

 

[Marc L]

For testing purpose, I need your 'big' workbook with the more numbers to sum,​

the maximum numbers in combinations (cell D4) can be 10 or even more …​

​

 

[Hussaan]

Find attached file with more numbers

 

[Hussaan]

For testing purpose, I need your 'big' workbook with the more numbers to sum,​

the maximum numbers in combinations (cell D4) can be 10 or even more …​

​

Yes maximum is 10

 

[Marc L]

With your initial attachment my new 'combination sum' well works, returning the 393 results in less than 0.1 second …​

​

With your new attachment, after 10 seconds it crashes … but I see some results and scrolling down, you guess what,​

it seems than for your list of 82 numbers (without duplicates) there are more than 65 536 combinations ! (> 500 000 …)​

​

So for testing purpose I must grab another computer with a more recent version than my usual test computer, to be continued …​

​

On your side, do you find something on web ?​

​

 

[Hussaan]

Find attached. I think it will help.

 

[Marc L]

Seems so far away from this subject …​

​

Anyway, on another computer after 3 minutes the procedure stops at the last row of the sheet so at row # 1 048 576​

and it seems there are 4 times more combinations for your last attachment so the question is what's for ?​

Do you really need 1 million - or more - combinations, what's the purpose ?​

If you really need more than a million so think about an Excel layout, a strategy …​

​

 

[Hussaan]

I will try to explain.
Currently we have big width papee reels which are available in 175 to 182 inch size.
We have customers requirements of sizes like 103, 90, 77, 50, 45... Inches as a mentioned in the excel file. Our objective is to cut the 175 to 182 inch big roll is small width rolls with and minimize the waste.
Is it possible if we reduce maximum number to 6 and reduce the customer numbers to 60, to make excel vba working?

 

[Marc L]

As yet stated, it's very not a VBA issue but just a basic mathematical problem !​

Just Excel is not the best tool as it needs more time to display the combinations than to calculate them !​

​

The bigger sum range, the more combinations …​

The more numbers in the list, the more combinations & the more time to execute …​

The more small numbers in the list, the more combinations & the more time to execute …​

So when you combine them …​

​

… like for 60 & 6 : the simulation stops again at row # 1 048 576 …​

It seems you do not have any idea about Combinatorial Calculations, see on web !​

​

So no new strategy neither new layout, stay with your original attachment as it is ?​

​

 

[Hussaan]

I am working on a new format and share it today.
Thanks

 

[Marc L]

Yes well think about your real need, do not forget anything as I will post a 'one shot code', I won't modify it.​

I will have to convert a child way two kids have found last saturday in a couple of minutes just operating manually​

(I gave them as an exercice) as it's easier & faster than finding an old pro archive and trying to modify it.​

As you can yet find all the necessary on web for a pro way, whatever for maths, VBA or any faster coding language …​

​

Which Excel & Windows versions do you use ?​

​

 

[Marc L]

Which Excel & Windows versions do you use ?

Another question :​

if the list of numbers to sum is not smart enough (duplicates, …) can it be directly corrected or it must stay as it is ?​

​