Finding the Closest Customers to a Branch in Excel

In order for the technique on this page to work, you must purchase and install Spheresoft Zip Code Distance and Radius Tools

Here's a simple way to quickly look up customers closest to a zip code. For instance, if you want to assign customers to a branch or find out which customers are closest to an event you are planning. Say you're starting with a spreadsheet that looks something like this:

To turn this into a customer lookup tool. Follow these steps:

1. Insert two rows to the top of the sheet.
2. In A1 put a header and in A2 put the target zip code.
3. In E3, put the header Distance from Target
4. In E4, put the formula =ZipCodeDistance($A$2, D4)
   you need to use dollar signs, so that when you copy, A2 is not changed in the formula.
5. Copy E4 to the rest of the column.

It should look something like this:

You could just sort the list to find the closest customers, but another way is to use Excel's Auto Filter.

1. Select A3:E3 (the header of the customer section)
2. Choose Data->Filter->AutoFilter from the main menu
   This will put arrows next to all of the headers
3. Click the arrow in E3, the header for Distance from Target
4. Choose (Custom...) from the list
5. In the dialog, set the left choice to is less than or equal to and then type 10 in the text box next to it.
   
   
   
   
6. Click OK.

It will look like this:

The list will contain only customers whose zip codes are within 10 miles of the target zip code. If you need to get back the whole list, click the arrow in E3 again and choose (All). To turn off Auto Filter, choose Data->Filter->AutoFilter again from the main menu.

If you have Spheresoft Zip Code Tools installed, download this sheet to see a live example.

(Back to the ZipCodeTools home page)

$65 - immediate electronic delivery

Not what you were looking for? Please send us your feedback!