How to Filter Address Lists Using Microsoft Excel

Microsoft Excel “custom functions” can easily carry out tasks related to address and zip code information, such as importing demographic data, checking address accuracy, even calculating driving time and distance. Custom functions, also referred to as User Defined Functions (UDFs), perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, LOOKUP, etc.

In this article we will show you an example of how custom functions, used in conjunction with Excel’s search and filter capabilities, can also be used to effectively filter addresses in a mailing list.

Let’s say we have a long address list in Microsoft Excel, and would like to mail marketing literature only to those addresses within a 5 mile radius of a store location in zip code 48906. The first step is to use an appropriate radius custom function in a new Excel worksheet, like this:

{= RADIUS (Zip Code, Radius Distance)}

For this example the formula is

{= RADIUS (“48906″, “5″)}

The custom function will return an array of data to the worksheet, where the first column of the array contains all existing zip codes within a 5 mile radius, and the second column contains their distance from the target zip. For this example we’ll assign a named range for all the returned data here called ZIPLIST.

The custom function used here automatically identifies all zip codes that fall within the radius by searching a local zip code database and calculating distances based on latitude and longitude. These calculations occur completely in the background, so the user sees only the resulting list of zip codes displayed in their worksheet.

We then return to the worksheet containing the address list and apply the Excel search function VLOOKUP to each address. The VLOOKUP formula determines which zip codes in the address list can be found in the ZIPLIST data range. For example, if the zip code of the first address is listed in cell D2, the formula is “= VLOOKUP (D2, ZIPLIST, 2, FALSE)”, where “D2″ is the cell location of the first zip code, “ZIPLIST” is data range being searched, “2″ indicates that if the zip code is found in ZIPLIST the data in the second column (the distance from the target zip) will be returned to the cell, and “FALSE” indicates that we are looking for an exact match of zip codes. If an exact match is NOT found, “#N/A” will be returned to the cell.

After we copy the VLOOKUP formula along the entire list, any address with a value greater or equal to zero means that it falls within the specified 5 mile radius and should be included in the mail campaign. Any address with “#N/A” is beyond the 5 mile radius and should be excluded.

For very long address lists, it is also helpful to use Excel’s autofilter feature. We just select any column heading in our address list, then from the Excel toolbar click on “Data”, then “Filter”, and then “AutoFilter”. Using the filter arrow on the column containing the VLOOKUP formulas, we select “Custom”, then select “is greater than or equal to” and input the value “0″. The autofilter shows only those addresses where the values are greater or equal to 0, in effect showing only those addresses within the 5 mile radius.

This is a good example of how a custom function can be combined with the built-in features of Excel to easily filter address lists. Custom functions can be used to automate complex tasks with no need to learn a new application or even leave the familiar environment of Excel. From importing customer demographic data to identifying zip codes within a radius, custom functions can be valuable tools for analyzing address and zip code information in Microsoft Excel.

Please see the YouTube video at http://www.youtube.com/watch?v=nOiu1Tpf3rM for step-by-step instructions of the filtering procedure described here. The author of this article, Betty Hughes, helped develop CDXZipStream”, an Excel add-in that provides zip code data and other demographic information using custom functions.