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.
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.
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
After a few minutes this sequence is shown with the shortest total distance.
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.
Read more interesting posts:
- A quicker A * pathfinding algorithm
- Finding the shortest path – A * pathfinding
- Build a maze
- Solve a maze
- Identify numbers in sum using solver
- Excel udf: Find numbers in sum
Download excel *.xlsm file