## Optimize pick path in a warehouse

*Article updated on August 11, 2017*

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:**

=SUM(D3:D18)

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.

### 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.

### 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**

### 6 Responses to “Optimize pick path in a warehouse”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

It is a great job, however the diagonal steps (1.41 meter) on the grid are longer than the vertical or horizontal steps (1 meter). That is not taken into account in this code, so the lenghts in the distance matrix are not exact. So, I was wondering whether it is possible to include the extra diagonal distances as well and how to do it in the code.

Krnja,

What a great question! I did not think of that.

I made these changes to the code:

and

You can download the workbook here:

Optimize-pick-path-in-a-warehouse-Krnja.xlsm

See sheet Paths, the old values are in cell range A25:Q41 and the new are in B2:Q17.

Great, gets it way more realistic. Another thing I noticed is that the distance matrix is not perfectly assymetric. Start to article 4124 for example. And the difference sometimes grows (to 10-20 metres) when there is no small aisles but more free paths like in the example below. If you have buildings and walls and you want the distance matrix of it:

https://oi57.tinypic.com/2pqw5s9.jpg

OK, I turned the heuristics to 0 to return to the classic Dijkstra algorithm and the table is way more assymetric as you can see in the link below:

https://oi62.tinypic.com/eb632r.jpg

So seems like a heuristic related issue. However, even with Dijkstra few are not matching as shown in the same link. As I'm a beginner with VBA coding, the exploring goes slow :)

Hi Kmja

First of all I should say Hats-off great job. secondly, I am very new to excel VBA coding and I am trying to understand if this application can be used for pallet racks in a warehouse. If yes, could you please help me understand.

Appreciate it!

Best Regards

Is there an easy way to convert the meters into feet in the code? I'm not entirely familiar with Dijkstra algorithm, and and VBA script is very arcane to me.

Thank you for this awesome script.