Author: Oscar Cronquist Article last updated on May 23, 2019



As you probably already are aware of I have shown you earlier a vba macro I made that finds the shortest path between two points. There are obstacles between these two points to make it more difficult.

The problem with that macro is that it could only show a path that moves up, down, left or right. So I had to change it so it can move in eight directions.



Instead of using 6 movements it now uses 3 movements, it can now go diagonal from A to B. This is more realistic, of course.

Calculating all possible paths between 15 locations

The following animated picture shows you 9600 storage locations, named item 1 to 9600. Each black dot is a storage location in this warehouse.

There is a start point almost at the top of this picture. I have chosen 14 random locations and the macro shows the shortest path and calculates the distance in the table at the bottom of this picture.

Optimize pick-path in a warehouse4

Find the shortest pick path

Now that we have all distances between 15 locations we can use the Excel Solver to find the shortest path. First we need to setup a sheet.

optimal path1

Formula in cell C4:

=INDEX(Items!$H$4:$H$17, MATCH('Optimize total path'!B4, Items!$G$4:$G$17, 0))

Formula in cell D4:

=INDEX(Paths!$C$3:$Q$17, MATCH('Optimize total path'!C3, Paths!$B$3:$B$17, 0), MATCH('Optimize total path'!C4, Paths!$C$2:$Q$2, 0))

Formula in cell D19:


Now it is time for the excel solver to find the optimal path. If you need more detailed instructions, check out this page: Travelling Salesman Problem in Excel Solver

Solver Parameters

After a few minutes this sequence is shown with the shortest total distance.

optimal path2

Optimal path

Here is the shortest path. It begins with the start point almost at the top and goes through all 14 storage locations and then back to start point.

Optimal path1

Read more interesting posts:

Get excel *.xlsm file