Excel Mapping States

Hi, Everyone! I have a situation here and I want to resolve it via excel. Excel File Attached. In Column A, we have driver home state. The column B is where the driver deliver the goods. Now we have to plan the (shortest route of driver such that the driver ends up in the home state eventually and the driver continues to the same cycle again. For example, lets say driver starts from WV and Ends up in NC.

Then from NC he can go to any state as given below(copies from excel), but we will prioritize(not necessarily NC-->PA as priority number(column C) is highest among all. The same step we will repeat from PA to Any other state(given in excel). Until the driver ends up home at NC.

NC-->PA
NC-->FL
NC-->OH
NC-->SC
NC-->GA
NC-->MI
NC-->NJ
NC-->NC
NC-->MD
NC-->KY
NC-->VA
NC-->AL
NC-->IA
NC-->MA
NC-->OK
NC-->TN
NC-->WV
NC-->CT
NC-->MS
NC-->CO

So in the end we will have a one best route for driver living in NC. Lets say that in this case hypotherical route would be (NC-->PA-->OH-->FL-->NC).

Throughout this process we have to keep in mind that every driver in all 50 states cannot go to all 50 states, as you can see that in case of NC, the driver can go to following states only(as shown in column B). Secondly, we have to priorities the driver next destination/deliver location based on the priority number shown in column C.
Exce Forum 1.PNG

NC-->PA
NC-->FL
NC-->OH
NC-->SC
NC-->GA
NC-->MI
NC-->NJ
NC-->NC
NC-->MD
NC-->KY
NC-->VA
NC-->AL
NC-->IA
NC-->MA
NC-->OK
NC-->TN
NC-->WV
NC-->CT
NC-->MS
NC-->CO .

Since we have 50 states in column A so we would find one single best route(Total 50 Routes) for all 50 states such that driver ends up home eventually.

Please let me know if it's possible via excel. Thanks and Regards

The ultimate goal is we have to complete the loop for driver back home making sure that we prioritize based on priority number. In case loop is not getting completed then we can choose the second highest priority for same location.

Coming to your First question "Is that a typo or does the system need to detect this and choose the next highest which is PA-->NY (priority 26)" ,The answer is The system need to detect this and choose the next highest whch is PA-->NY .

Regarding Your second question: "But then the highest priority for the NY--> ?? delivery is back to PA (priority 57) and the second highest priority is NC (priority 34) the starting point." the system should choose the second priority which is to NC and starting point and the driver will be back home.

System shouldn't keep track of previous delivery stops. If we look into it then one driver won't reach the same location twice if we want the driver to reach home(starting point).

Let me know if I have missed anything. And thanks for your questions.

Is it possible thing via excel ?

Yes you are right. We want' to avoid never ending loop.

Okay. Is it possible that we don't display the routes that don't end up at the same state where they started?