We’ve just introduced a new free template that can geocode (get latitude and longitude) for long lists of addresses, and then use this information to sort the addresses based on their distance from a selected target location. Our “Geocode and Sort by Distance” template is very useful for a variety of applications:
- Find the closest customers to a store location (or the closest store locations to a customer)
- Identify the closest treatment facilities to a patient (or the closest patients to a treatment center.)
- Find the closest delivery points to a warehouse location (or the closest warehouses to a delivery point.)
- Determine the closest trash collection sites to a waste disposal facility (or the closest waste disposal facilities to a trash collection site).
You get the idea! The advantage of this template is that it allows you to process lots of addresses, and get detailed distance data around a target, all very quickly in Microsoft Excel. For example, let’s say you have a list of addresses for a national retailer, along with a list of addresses of a janitorial firm that has a new contract to service this retailer. Each retail location needs to be covered by two service locations, including a back-up. Just copy-and-paste into the template all the addresses of the retailers and janitorial locations (there are two separate worksheets for this), and allow the template to do all the hard work of the geocoding and distance calculations. The Microsoft Excel sort function can then easily list all janitorial locations in order of their distance from a selected retailer.
The template uses the functionality of our Excel add-in, CDXZipStream, along with the geocoding ability of Microsoft MapPoint, to perform the calculations. As an added bonus, the “match” quality for each address is also provided by MapPoint, which allows the user to evaluate the validity of the input address data. Calculations are based on straight-line or “as the crow flies” distance, which is a fast calculation and allows for analysis of tens of thousands of addresses.
In cases where a more accurate analysis is required based on actual driving distance, this template can be used to do a first-pass determination of closest locations based on straight-line distance to the target. (Straight-line distance is a much faster calculation than driving distance, and is more practical when there are many locations in the analysis.) The CDXZipStream routing function (CDXRouteMP) can then be applied separately to calculate driving distance just for the closest locations. This secondary calculation is not currently part of the template. For a more detailed description of this two-tier calculation, please see the article “CDXZipStream Straight-Line and Driving Distance Calculations“.
All of our free templates can be downloaded from the links page of our website, and are compatible with the free trials of both CDXZipStream and Microsoft MapPoint, where applicable. Please note that the CDXZipStream trial version is valid for 30 days and is limited to 1000 calculations per Excel session.
Also available for purchase is our Bulk Radius template, which performs the same distance analysis but also generates reports for every target location, showing only the closest locations within a designated radius distance. Please see our article “Store Locator and Bulk Radius Analysis” for more information.