Building A Truck : Even Pallet Quantities

[soccerletsgo]

Hi All,

This is my first post and I'm hoping I can get some help as I haven't been able to find something similar online.

In my job I need to create truckloads. Each truckload is to have X amount of pallets based on what the item is. I have to split up my inventory based on lot # and pallet quantity. I've attached a sheet with the rules and an example of a "perfect deployment" (multiple truckloads for the quantities I need to ship). You are allowed to mix items in a truck as well as lot #'s. However, each truck must be EXACTLY the maximum (either 26 pallets or 23 depending on the item). Any odd quantities must be on the last shipment and all grouped together.

I don't know if that is the best explanation but I'm attaching the spreadsheet and am more than willing to answer Q's. I do this often so any way to automate it would be helpful.

[shank_mis]

Hi,

I have solved the problem upto a certain point. After that, there are many solutions.
Using solver might help since, this is also an optimization problem.

This is comparatively a bigger problem and consuming a lot of time to develop a solution.

[soccerletsgo]

This is super helpful - Thank you!!

[Alf]

Just to show a possible solver setup I've made a small solver model that shows a solution for 5 truck loads. The excel solver has a bit of problem that finding an optimal
solution so I had to set a time limit for the solving time. When the "Show trial Window" pops up click on "Stop" and then "OK".

The solver result can be "sub optimal" as any result may be when one finds a solution based on a number less than the total number of components.

I assume that the all the lifting in "Example" tab must be lifted so to see if there was any big difference between lifting at two times 5 loads plus one at to loads. I got the results:

Comparing the result from the excel solver setup with the in one go for open solver I did not see any difference on the average load result so I can't see that the excel solver setup gives a sub optimal solution in this case.

More info in uploaded file.

To find the solution to loading on tab "Example_2" the excel solver will not be sufficient. For this one need to download and install the freebie "OpenSolver" build by the university of Auckland NZ.

Link https://opensolver.org/installing-opensolver/

Alf

[soccerletsgo]

WOW! thank you!

[Alf]

You are welcome and thanks for feed back.

Alf

[cmeredith73]

Hi if i had a product mix like this - could the pallet builder work the same way? Im looking to find a way to based on the mix

Attachment 769868

[Alf]

Hi cemeredith

Your posting is a no no according to forum rule 4

4. Do not post a new help request in an existing thread.

Also posting in an old thread marked "Solved" will not attract much attention. Post a new thread with a descriptive heading and upload a file showing your data and what you wish result to be is a better way to get a solution to your problem.

If you think it helpful you could include a link to this thread to show what kind of solution you would like.

An uploaded link to an imager does seldom work. Clicking at your added attachment generates this message.

excel_link.jpg

In order to test if an attachment works first save you message then click on attachment. If error message like above edit message, remove old attachment and add it a second time. Save and look at posting. If attachment now shows you have succeeded and other members will be able to see it.

Alf