Forum Discussion
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.
appreciate your help
8 Replies
- 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)
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.
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.
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.
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.
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
I forgot to attach the file
please check it
