Solver Transportation solution

[Normie]

we are trying to cut our transportation costs for loads between our 5 consolidation buildings I have attached a file with the total miles between each Con. building as well the number of pallets being sent between the buildings what I am trying to do is maximize the trailers to 27 pallets by using the least amount of miles, can someone help in pointing me in the right direction using Solver......or is Solver the right tool?

[shg]

Wow -- that seems like a hard problem to set up for Solver, or to model in code.

Start at whatever location the trailer camped for the night.

Pick up some fraction of the deliverables for other locations, up to the available limit. Drive to the next location; drop off its deliverables and fill the truck. Rinse & repeat.

In each step, you need to optimize both the pickups and the order of locations visited.

Ignoring that you have a second trailer, it takes several circuits to complete the deliverables.

You might take this the the operations research department of a good college.

[Graham Griggs]

This is a classic problem that has been around for hundreds of years - the good news, if it is that, is that it cannot be solved in general

However for a specific situation it can be by sheer trial and error (quite quick with a spreadsheet)

You don’t say whether it is a one off problem, or whether it is daily, weekly etc

Also are the sites all in a straight line? if not it makes a difference how they are laid out? That makes a big difference. Also time is (i guess) more important than miles, so how far are they apart in time? (Eg if you have full truck A to B then go by the quickest route, but if it is not full, it might be worth going via C if it is not too much of a detour

Finally your container numbers do not help. Where are they now and where do you want them? Totals of each are no help. If you post them in a reply. I can find you a good solution quite quickly.

It is certainly not a problem for Solver, it has been puzzling mathematicians for centuries. so I will never be able to do is prove that my solution is the best one


I need something like the attachment

[shg]

@Graham: The numbers don't identify containers, they identify (consolidation) buildings.