Thanks Joel, Im pleased its going to be possible.
Ive got most of the data not quite sure where to take it next
Ive posted my spreadsheet here please can you take a look

http://www.quickfilepost.com/downloa...3271618d77a157



"Joel" wrote:

The alogorithm you choose wil depend on how much time you want to use to get
the results. In your cas time is not a factor. therefore I would try every
combination a keep track of the previous shorest distance results. You also
want to make sure you don't get into any endless loops.

Usally a recursive method is the best algorithm in getting these results.
Start at the source location the follow these steps.

Create an 3 dimensional array of all the routes. The size of the array
would be

Routes(Number of cities, maximum number of routes from any one city,2)

The 2 at the end is for the distance and the new city name.
If you had 3 cities , NY, Chicago, LA

1 = NY
2 = Chicago
3 =LA
Start/ Destination
Route
1,1,1 2(Chicago) 1,1,2 2000 miles
1,2,1 2(LA) 1,2,2 3000 miles
2,1,1 1(NY) 2,1,2 2000 miles
2,2,1 3(LA) 2,2,2 1500 miles
3,1,1 1(NY) 3,1,2 3000 miles
3,2,1 2(Chicago) 3,2,2 1500 miles

making 2nd array of the current route.


Recursive algorithm
1) Select each of the routes from currect location one at a time
2) Indicate on the array the route chosen
3) call recursive algorithm again passing the latest copy of the array of
routes and the new desintation location. Continue to all the routes are used
or you hage exceeded the previous shortest distance.

"excel_lover" wrote:

Hi,
Im trying to automate a task and to optimise some road routes.
I have a matrix, the matrix is a ready reckoner to help calculate milage
between a source and its destination; the top row of the matrix has the
destination and the left column has all the source sites.
Each destination has an annual input capacity and each source has an annual
production quantity aswell, these are kept in another two tables.
I need to have excel work out the most efficient routes taking into
consideration the source production and the destination site capacities.
Ive been trying to find a suitable algorithm and have looked at
Floyd-Warshall, Dijkstra's & Johnson's but none seem right.
Please can anyone give direction on this or possibly help with the code.
Thanks for your help.