Help using solver for product allocation

[cg2021]

Hello everyone,

I am trying to use solver (simplex method) on excel to realize the following operation:

Allocate grams of a single product into different sized vials (depending on demand of each vial size) that only a max amount of vials fit in a box.

I grabbed a model template that showed product mix, and have been working based on that framework, but I am still having a lot of trouble. Is there an easier way to do this?

Can anyone help me, or better yet take a look at my file?

Thanks in advance.

[Alf]

Can anyone help me, or better yet take a look at my file?

Yes just upload a file giving the constraints size, demand and max amount in box.

Click on "Go advanced" button and click blue text lower half of page "Manage Attachment" to upload file.

Alf

[cg2021]

Thank you very much for your reply Alf.

Please find the file attached. Let me know if you have any questions.

[Alf]

So each box contains two layers of tubes and since each layer has the same maximum number of tubes does this mean that you can mix 0.5 cc, 1 cc and 2 cc?

As the demand for 0.5, 1, 2 are (355 + 410 + 35) = 800 would that then be 6 boxes (6 * 120) 720 + one layer of 60 and one layer of 48 (the 5 cc) the 7th box and an eight box with 20 in one layer and 15 of the 5 cc in the second layer?

Alf

[cg2021]

Yes, that is correct Alf, you can mix tubes of .5, 1, and 2 cc (in layer 1 and layer 2). Plus, you can have one layer of .5, 1, and 2 and another layer of 5 cc in the same box. What cannot be done is mix .5, 1, 2 on the same layer as the 5 cc.

As far as the demands, you are right.

Thanks again. Please let me know if you need anything else

[Alf]

I'm not sure solver is the right tool for this kind of problems. As I said before and you confirmed 0.5 cc, 1 cc and 2 cc can be mixed. The 5 cc can't be mixed with the other tubes in the same layer but two different layer is ok.

So in your uploaded file you have 800 tubes 0.5 to 2 cc and 63 of the 5 cc ones and that gives 6 boxes of 0.5 to 2 cc and a reminder of 80 tubes.

A) Having 80 tubes 0.5 to 2 cc you can add all to one box with 60 in 1st layer and 20 in 2nd layer.

The 63 tubes of 5 cc can also be put in a box with 48 tubes in 1st layer and 15 in 2nd layer.


B) You can also have a box with 60 tubes 0.5 to 2 cc in 1st layer and 48 tubes 5 cc in 2nd layer

and a last box with 20 tubes 0.5 to 2 cc in 1st layer and 15 tubes 5 cc in 2nd layer

I can't see any difference between case A and B you still need the same number of boxes. And in case A you have two boxes with one filled layer and second layer partly filled and in case B you got one box with both layers filled and on box with both layers partly filled.


Alf

[speed21]

Yeah, both cases are the possible.

Do you know if there could be a solution to find the optimal mix, or all possible mixes using visual basic on excel instead of solver? I ask this because as you said, solver may not be the best tool for this.

What do you think are my current options to finding a solution to this type of problem?

Thank you again for the kind help.

[Alf]

Still not sure what you me by "optimal mix", I would say it was using as few boxes as possible when packing product for transport but I could be wrong.

So I set up a solver model for you. Some information in the file about the how and the why. Again I'm not sure this is the right setup for what you are looking for and since I'm not sure of what you mean by "optimal mix" I have not tested this model as thoroughly as one should do.

Alf

[cg2021]

Alf,

This is perfect, thank you. It is exactly what I was looking for.

Again, many thanks, really appreciate it!

Best regards,
Christian

[cg2021]

Sorry to bother you again Alf, but I did notice something.

I have attached the file with my comment regarding one box of 5cc with an empty second layer where another box with only one .5, 1, 2 cc filled layer too. In this occasion they could be combined. Is there a way to do that?

By optimal mix, I mean that I can pack everything in the most efficient way saving the most space

[Alf]

Working on a solution for this so it will take a day or two to solve it (if I manage). Will be back with good or bad news.

Alf

[Alf]

Here is the "old" model with a modified macro. I've tested some combinations like only 5 cc tubes or only 0.5, 1, and 2 cc and as well
as Layer 1 with 5 cc and Layer 2 contains only 0.5, 1, and 2 cc and as far as I can see the model works as specified.

Still the could be combinations that I have not tested and possibly the model will not work as expected. If so post a message in this thread.

Your cost model is not up to date based on this more compact packing model as it don't see if there is a difference between layer 1 and 2.

If I got time I'll have a look and see if I can come up with something. One could perhaps make a "map" based on a packing order.

Starting with 5 cc, then 0.5 cc, then 1 cc and finally 2 cc.


Alf