New Contributor

need cells combination of cells that will follow 2 conditions rules

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.

8 Replies

Re: need cells combination of cells that will follow 2 conditions rules

you can easily use solver

I have set up the model for you

just change the car lengths

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

hope it is clear

Betreff: need cells combination of cells that will follow 2 conditions rules

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.

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.

Betreff: need cells combination of cells that will follow 2 conditions rules

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.

Betreff: need cells combination of cells that will follow 2 conditions rules

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.

Betreff: need cells combination of cells that will follow 2 conditions rules

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.

Betreff: need cells combination of cells that will follow 2 conditions rules

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.

Re: need cells combination of cells that will follow 2 conditions rules

I forgot to attach the file

Betreff: need cells combination of cells that will follow 2 conditions rules

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)