Macro for solving the least costly shipping route

vbaforexcel101

New Member
Joined
Dec 13, 2012
Messages
27
Goodmorning all,

My challenge is the following. Suppose, I have clients who trade goods with each other all over Europe. However some clients prefer the option for the package of their counterpart to be checked by a middleman before the trade goes through. Therefor I would offer 4 possible shipping stations in Belgium, Netherlands, France and Germany.I have found a way to calculate the cost of the shipping route (see C56:N67) based on the middle station (variable cell = D52 and can take values between 1 and 4, the name of the corresponding middle station is in C52):

https://docs.google.com/spreadsheet/ccc?key=0AvqjcsOrtLCRdE5pc1czWG5NM1NCQVljRzlCYkdqeEE&usp=sharingNow


In a next step, I would like to have a macro who creates a table and solves for the least expensive shipping route, and returns which middle station one should use in order to minimalise the cost of the shipping route.

Many thanks in advance for your help. Enjoy!
 
Last edited:

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
is this really a macro ?

I ask because

If you are shipping direct, then one cost

If you ship via middle man then you have four potential costs

and from those four destinations, an onward second cost

sounds very complex to set up

you will never ship to the middleman, and then ship again if at the same location (I would suggest), and I can't see you would ship to one middle, then onto one of the other three primaries
 

vbaforexcel101

New Member
Joined
Dec 13, 2012
Messages
27
Indeed correct but I think it would be most practical to have a macro once I start adding additional shipping stations and destinations.

To keep a long story short. For every individual cell in the range C56:N67, the macro should calculate all 4 different shipping cost solutions by changing the variable in cell D52 (1, 2, 3 and 4). Once he has done this he should remember which of the four inputs of cell D52 resulted in the cheapest shipping route and put the value of cell D52 in the cell where the shipping cost was calculated.

Hope this helps
 

vbaforexcel101

New Member
Joined
Dec 13, 2012
Messages
27

ADVERTISEMENT

you will never ship to the middleman, and then ship again if at the same location (I would suggest), and I can't see you would ship to one middle, then onto one of the other three primaries

This is not necessarily true. E.g. A German ships to a Frenchman through the French middleman. In this case the item will first be shipped to the French shipping station and then from the French shipping station to its final destination in France.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
France would not be one of the other three primaries, but i take your point

sorry I can't help, I would have done it on graph paper
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top