need cells combination of cells that will follow 2 conditions rules

Copper Contributor

i have 10 cars that need to fit in a car transporter truck. I have a limit on the length of the truck, which can take 5 cars in upper level and the rest 5 in ground level. i need a formula that will take the 10 car lengths and give me all possible 5 car combinations that dont exceed the truck length limit, while the rest 5 cars will also not exceed the length limit.

appreciate your help

8 Replies

@paranoir1986 

 

you can easily use solver

I have set up the model for you

just change the car lengths

then go to data --> solver --> optimize

if you did not add solver, add it, see how to do it using youtube

hope it is clear

@paranoir1986 

 

Here is a small proposal for a solution ... very simple but clear.

 

If I can recommend you, add a file (without sensitive data) and explain your plan by adding something to your question in the file.

This means that you can be helped faster and more accurately and it is made easier for the helpers to offer you a tailor-made solution.

 

Hope I was able to help you and would be happy to know if I could help you.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE 

 

Thank for your proposal.

Actually, the transportation truck can take 5 cars at the upper level and the rest 5 at ground level.

Every level has a limit of 19 meters length in car load.

 

Therefore i need a formula that will identify all 5 car (out of 10) combinations that will have a maximum sum of 1900 (19meters shown in centimeters) in order to fill upper level for example, while at the same time,  the rest 5 will also have (as group 2) a maximum sum of 1900 in order to fill ground level. 

 

In my opinion, its pretty clear as a thought, but can't get close to a solution as far as excel programming is concerned. 

@paranoir1986 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE 

I can't thank you enough for your immediate support.

 

The point here is the following:

I insert 10 values for the lengths of the cars.

Excel provides me the combinations as follows:

 

eg PUT CAR 1 +CAR 5+CAR3+CAR 9+ CAR10 together as the total length is less than 1900 

and, at the same time, because the rest of the cars CAR 2 +CAR 4+CAR 6 +CAR7 +CAR8 have a sum of length also less than 1900. So group 1 could go upper level and the other goes down.

The issue here is not to calculate the sum, but create automatically two groups of cars that won't exceed the max length given. In the end, i have all the combinations of car groups, which would both have a limited total length.

 

My next step would be to check if the upper level car and ground level car, dont' exceed the maximum height. But, that is easier. I already have a solution of the height.

Hi @paranoir1986

 

I wrote a VBA macro for you.

Don't touch the blue cells. They will be automatically populated when you run the code. 

If your max length (cell C2) suddenly turns red, don't bother, it's mathematically impossible to fit your 10 cars.

This will only populate the first row but I hope you'll figure out that the remaining cars goes on the second row.

I forgot to attach the file

please check it

@paranoir1986 

 

It is not quite finished (sry no more time) but with this you can fill the first level and the second level would be the rest.
However you look at yourself, if that comes closer to your ideas, we can continue from there.
It is of course VBA is also a possibility, like Mr. Ramiz_Assaf  sent you with a wonderful example.

There are many ways that lead to Rome, so there are many ways to the solution with Excel :)
 

Sorry that I took your time and couldn't give you a quick solution.

 

Wish you a nice day.

 

Nikolino

I know I don't know anything (Socrates)