Travelling Salesman problem

[ashishkhurana93]

As I can't share the actual data, so I am mocking this up in dummy data (see attached data)

A bus travels from one bus stop to other. Bus departed from the Portland Amrak Station and arrived at the Mount Pocono depot. Bus second trip departed from the Mount Pocono depot and arrived at the Indianapolis Downtown.

How to determine the departure and arrival location for 257th trip

[MrShorty]

It's not clear to me exactly what you are trying to do or what part of the program you are having trouble with.

I recall this conversation from some time ago (https://www.excelforum.com/excel-for...el-solver.html ) talking about shortest path problems. As I understand it, the traveling salesman problem is, in many ways, a special case of the shortest path problem where you start and end and the same node and visit every node in the map. I would probably start something like this by understanding how to program a shortest path algorithm, then make it work for the specific case of the traveling salesman.

A quick internet search found this page specific to the traveling salesman problem: http://blog.excelmasterseries.com/20...blem-with.html

As near as I can tell, your specific list has only one path in and one path out of each node, so there is only one valid path through the list. Is that representative of your actual problem? If so, it might be mostly a lookup problem where you:
1) Enter the desired initial departure location. (maybe in K2)
2) Lookup function to return the arrival node based on the departure node. =VLOOKUP(K2,A:B,2,FALSE)
3) Copy arrival node to departure node. =L2 in K3
4) Copy lookup function into L3
5) copy K3:L3 down to the bottom of the list.

From there the nth leg of the journey is simply the (n+1)th row of this table.

I think if you clarify your requirements, we should be able to use one of those as starting points to find a solution.

[Hydraulics]

Dayton Travel Station -> Baker Station

C2 is the first trip, so we can write 1.

Then, in the same column, use the formula

=INDEX($A$2:$C$400,MATCH(A3,$B$2:$B$400,0),3)+1

Finally, add some conditional formatting.

HTH,

Francesco