Speedy Route Tips and Tricks #5

Applying Optimal Route Results To A Spreadsheet

Many customers have spreadsheets containing rows that have an address column, and then many other columns containing relevant information to the row. It's a common requirement to want to apply the optimal route calculation result of the address column to the rest of the spreadsheet.

Consider a spreadsheet containing addresses, and related contact names and phone numbers for each address:

Existing Spreadsheet

Lets go through the process of altering the spreadsheet to facilitate automatically applying the results of an optimal route calculation to the rest of the spreadsheet.

First, select the addresses of the spreadsheet and copy to the clipboard:

Copy Spreadsheet Addresses

Then paste the addresses into Speedy Route (please see our previous Bulk Add Multiple Locations article for more details). In the example below the Start Location has already been filled in before the Bulk Import, so that the pasted addresses become the Route Locations:

Paste Spreadsheet Addresses

Once the Bulk Add operation has completed, Speedy Route now has the imported addresses:

Bulk Added Spreadsheet Addresses

The imported addresses are "fully qualified", i.e. full addresses including regional and national details. We need to export these back to the original spreadsheet as an extra column for reference, so click on the "Export" tab and uncheck every checkbox except "Location":

Export Fully Qualified Original Addresses (except the Start Location)

As shown above, the Export tab lists the Start Location and Route Locations (please see our previous Export Tab article for more details). We originally only exported the Route Locations from the spreadsheet, so select all but the first fully qualified address and copy to the clipboard.

Now add a new column to the original spreadsheet and name it "Full Address". Paste the fully qualified addresses into it to match the original rows:

Original Spreadsheet With Additional Fully Qualified Addresses

Add another new column named "New Order" and also paste the fully qualified addresses into that too. Add a further column named "Swap Order" into which we will add an Excel formula:

Additional Spreadsheet Columns

For each cell in the "Swap Order" column, add an Excel formula similar to:

=MATCH($E3,$F$3:$F$9,0)

The exact content of the formula will depend on your own spreadsheet layout, but the intent is that for each cell in the "Swap Order" column, the formula will MATCH the content contained in the cell of the same row from the Full Address column against the entire content from all the rows in the New Order column. The purpose of this will soon become clear!

Repeat this process for all the cells in the "Swap Order" column, so that the final result looks like this:

Spreadsheet Formulas

As can be seen in the above example, because the "New Order" contents are currently the same as the existing order, the "Swap Order" column counts from 1 to 7 as the order is currently correct. The original spreadsheet has now been modified so that the results of an optimal route calculation can be automatically applied.

Now we can click the "Calculate Route" button in Speedy Route to calculate the optimal route between the route's locations:

Calculated Optimal Route

This is the result we want to apply to the original spreadsheet. Go to the Export tab and set the same options as before. Then select all but the Start Location and copy to the clipboard:

Export Optimal Route Locations Order

Paste the re-ordered fully qualified addresses over the contents of the "New Order" column of the spreadsheet:

Pasted Optimal Order

You can see above that the contents of the "Swap Order" column have automatically recalculated to show the order that the rows of the spreadsheet should now be in.

Select all of the data rows, and choose the Excel Sort function, specifying that the rows should be sorted by the "Swap Order" column:

Sort Rows By The "Swap Order" Column

Finally, now perform the Excel Sort as configured. The contents of the spreadsheet will be sorted to match the new optimal order, and the associated row data will also be sorted:

Sorted Spreadsheet

In the above example, Sheffield was originally the second row, and has moved to the last row in line with the optimal route order, and Fred and his phone number has also moved with the Sheffield entry.

Happy optimising!