Route Optimization

[BOUCHOUATA]

Hello everyone ,

I have below data which is related to the location of some customers who will be delivred by my driver. I would like to determine the best route of delivery that my driver should follow :
Here the criteria is customer distance from my actual location , i want to write a macro that select the customer A , D , B.
Please support to proceed so.

Code Client	Client	Adresse	Zip Code	Distance (KM) from Point A	Closed By
CL1	Customer A	Rue num 1 town	200001	15	03:00
CL2	Customer B	Rue num 2 town	200002	30	03:00
CL3	Customer C	Rue num 3 town	200003	50	03:00
CL4	Customer D	Rue num 3 town	200004	20	01:00
CL5	Customer E	Rue num 3 town	200005	80	03:00

Best regards
Taoufik

 

[Eric W]

Welcome to the MrExcel forum!

I don't think you even need a macro. Just sort your table by the distance column.

This really only works if the towns are in the same direction. If they are all scattered around, this becomes more difficult. This is what GPS can do. Look up "Shortest Path Problem" in Wikipedia. There are multiple algorithms to solve it, but it's doubtful that anyone here will spend the time to adapt them to an Excel macro due to the difficulty and time needed. But there are websites that will do this for you, like Mapquest.

 

[BOUCHOUATA]

Hello Eric ,

Thank you for your support and suggestion. I will look for other resources.

Best regards
Taoufik