Need help with Solver

[krot84]

Hello
I don't know if i post in the right forum so please bear with me
I'm trying to develop a module for use at work.

This is my problem:

I have "N" products , each product weighs "X".
My job is to transfer ALL the N products into the least amount of containers and the total weight of every container must be less than a number which will be provided - let's call it "n".

I have some knowledge in excel 2010 but somehow i'm unable to solve this problem.
Here the link for the problem:
https://dl.dropboxusercontent.com/u/...78/Solver.xlsx

I appreciate all the help i could get.

Thanks

[ragulduy]

Sounds like a read of this might help:
http://en.wikipedia.org/wiki/Packing_problem

[krot84]

Thanks, I've read it, however it's not what i was talking about.
The link you've sent talks about packing geometrical shapes without any reference to weight of shapes.
The weight is as important as the number of the pallets I'd like to put into the container.

[ragulduy]

It's the same thing really, the weight is just an alternative constraint to using a geometrical constraint. That link is kind of the overview of the whole "packing problem" area of mathematics but if you do a bit of digging it would probably turn up something you could use.

You should probably start off looking at a "bin packing problem" and replace volume with weight, or look at the "knapsack problem" and assign equal value to every item.

[shg]

If the problem is is just weight, not shape, then it's equivalent to a 1D cut list from fixed-length pieces of stock. There's a workbook with VBA- and formula-based methods at https://www.box.com/shared/uhrjy318l1

[krot84]

Thanks, I'll try it.

[Alf]

If the problem is is just weight

In the example at https://dl.dropboxusercontent.com/u/...78/Solver.xlsx

there was a max weight of 27000 and max 20 pallets in every container so it the problem just weight or is it weight and number of pallets?

Alf

[krot84]

You're correct, as i mentioned in the example file, there are two constrains the max total weight and max number of pallets.

Thanks

[Alf]

Just for fun I set up a solver model based on your problem. This is not the solution you are looking for. I'm doubtful that the "normal" Excel solver can tackle your problem because you are talking about an unspecified and probably huge number of products. For this kind of problem you may probably have to buy the more powerful Solver models that cost about 3500 USD.

The model I build only handles 9 products and 4 containers still it takes a bit of time for solver to find a solution.

The number of containers is based on my guessimate so there is no sophisticated mathematics behind my choice. Tested model with "Simplex LP", "GRG Non Linear" and "Evolutionary" solver engine. All works but result differs a bit. The "Simplex" and "Evolutionary" engines are the fastest. The "GRG Non-Linear" takes quite a long time to find a solution.

Sorting the in data i.e. highest weight and highest number of palls to lowest weight lowest number speeds up the time it takes to find a solution for all 3 engines.

Alf

[krot84]

Alf that looks pretty close to what i was talking about.
Tell me please which cell is the "target cell" ?
My guess is that the changing cells are - E4-H12?
And the constrains are I15-I16 ?
What does constrain 1 mean ?

Thanks !!

[Alf]

C18 is target cell and yes E4 to H12 is cells to change.

Constraint 1 is that the sum for each row I4 = SUM(E4:H4) should be equal or less than 1. This to ensure that product A can only be placed in one container.

If you look at the Solver_setup.png file you see that range I4:I12 is set to be equal or less than K4 i.e. 1

From the solution you also see that I11 is 0 so product H is not taken care of for that you need an extra container.

Alf

[krot84]

I see now.
I've tried to play with the values of table a little - add more products and more containers, so i changed all the formulas together however when i enter the Solver it doesn't let me change the line " $E$4:$H$12 = binary " to the larger range I've created " $E$4:$K$15 = binary " it says that "the container must be a number, simple reference, or formula with a numeric value". What am i doing wrong here?

Thanks

[Alf]

What am i doing wrong here?

Don't know have a look at my "extended" solver model and see if you can find the problem. If not so upload your file.

Instead of setting "target cell" to max it's possible to set it to a "value of" i.e. the number of products.

Again this setup is a trial and error method as I've got no model to tell me the number of containers needed for a certain number of products as it's also dependent on weight and number of pallets.

So I guess I would start with a number of containers (perhaps 1/2 the number of products)and if solver finds a solution I would then remove one container and try again to see if solver finds a solution ......

Alf

[krot84]

Now i see what you're talking about.
Everything works just fine.
Thanks a lot Alf

[Alf]

Thanks for feedback and rep!

What am i doing wrong here?

Editing solver constraint you can use the "Change" button in the "Solver Parameters" window except for constraints like "binary" and "integer". If you try to edit those you get the "the container must be a number, simple reference, or formula with a numeric value" error. To change these you must delete the old constraint and then add the new!

Some thoughts about your problem. If this is a daily occurring planning problem I would probably use a workbook with only two sheets.

The first sheet would be used for my in data i.e. Product, weights and pallets. The second sheet would be my solver model set up as a matrix with the same number of rows as of columns (preparing for a worst case scenario i.e. only one product in each container).

Outside this matrix I would have all the constraint and the target cell.

I would use two macros, the first one would clear the in data sheet so I could enter the new data, product name, weight and pallets.

After adding new data I would run the second macro that would ask for a starting number of containers and the number of products. It would then copy the values from the in data sheet to the solver matrix and set up solver to run in a loop using starting number of containers for the first loop and use number of products for the last loop.

I would use “Max value” for target cell because then solver would always find a solution then check if target value = number of products.

If not so macro would increase container by 1 and test target value = number of products.

When this condition is fulfilled loop is stopped and solver result shown.

Alf

Ps As this seems to solve your probem could you please mark your thread "Solved"

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

[krot84]

Alf
Sorry but I'll be needing some more help just to make it more difficult.
Here's my edited sample https://dl.dropboxusercontent.com/u/...78/SOLVER.xlsx
What i need now is to add a constrain(s) which as follows:
Some of the products can only be organized in one row in a container i.e. i cannot put any other product on top of them, however they can be in the same container together.
Tell me please how can i add this constrain to certain products ? - I assume that the container can contain <=40 pallets on two rows, that means <=20 pallets per row.

I apologize for driving you crazy with my problems.

[Alf]

Some of the products can only be organized in one row in a container i.e. i cannot put any other product on top of them

Can you do it the other way round i.e. putting some other product in the bottom and the "sensitive" product on top of the container in one row?

Perhaps you could have two "kinds" of containers one with 40 pallets and a multiple of two with only 20 pallets. That should be no problem as long as you set up the appropriate constraint for each kind of container.

Alf

Ps Could you use the forum facileties for uploading files

How do I attach a file to a post?

To attach a file to your post, you click on "Go Advanced" page and not "Post Quick Reply" this will take you to the "Replay to thread" page

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the "Reply to thread" screen. Write your comments and finally click the "Submit Reply" button.

[krot84]

Can you do it the other way round i.e. putting some other product in the bottom and the "sensitive" product on top of the container in one row?

Please explain this part i didn't quiet understand...

Perhaps you could have two "kinds" of containers one with 40 pallets and a multiple of two with only 20 pallets. That should be no problem as long as you set up the appropriate constraint for each kind of container.

I don't know about that, you see, i want to keep the number of containers to minimum, is it possible with this approach ?

Here's the example again, sorry for the trouble
SOLVER.xlsx

[Alf]

Please explain this part i didn't quiet understand...

Let's assume that product D is the "sensitive" product so could you first load 9 pallets of T+TF and then top the row up to 20 with 11 pallets of D?

i want to keep the number of containers to minimum, is it possible with this approach ?

If you set up your model with 2, 4 or 6 twenty rows containers you can add these to make 1, 2 or 3 "normal" i.e. 40 pallets containers. A setup like this could make it easier to separate the "sensitive" products.

Alf

[krot84]

Let's assume that product D is the "sensitive" product so could you first load 9 pallets of T+TF and then top the row up to 20 with 11 pallets of D?

Yes i could but how do i do it in excel ?

If you set up your model with 2, 4 or 6 twenty rows containers you can add these to make 1, 2 or 3 "normal" i.e. 40 pallets containers. A setup like this could make it easier to separate the "sensitive" products.
Alf

Let's get back to this after i understand how to do stage one

Thanks

[Alf]

Yes i could but how do i do it in excel ?

You don't do that because if you get a solution where a 20 pallets container has 9 pallets of T+TF and 11 pallets of D you just add an instruction saying that T+TF should first be loaded then add D product on top of that. As long as you only have a container with only one row you could always add the instruction that the "sensitive" product should be loaded on top.

If you really wish solver to model the loading of containers then you must model each container in great detail.

Alf

[krot84]

I do want the solver to model the containers, however I don't know the formulas i need to use in each cell.

[Alf]

I do want the solver to model the containers, however I don't know the formulas i need to use in each cell.

Neither do I so I’m sorry but I don’t think I can help you any further.

You could try posting your problem in the “Commercial Services” forum where you will get help from the top forum gurus and moderators. This service is not free you do have to pay I fee first before you get any help.

Alf

[krot84]

Ok Alf, thanks for your time

[Alf]

Second thoughts as solver is not up to it there is also the possibility of transfereing the solver result into a loading plan for the containers.

I did one manually but it could be automated by using a maro.

Alf