Most of our customers who use our Microsoft Excel add-ins CDXZipStream and CDXStreamer for address and location analysis, are working with a lot of data in an Excel worksheet.  And if CDXZipStream or CDXStreamer are used to provide additional information for each point of data, it can be quite a task managing and analyzing all the results.  Here are some tips on how to approach data in a large Excel worksheet, particularly on how to find the data you need within a large data set.

First, if the data returned from the add-in is in formula form, get rid of the formulas by copying them to values.  Just select the area containing the formulas, and use the paste special option to paste values only.  The formulas will be replaced with their values; if a worksheet contains a lot of formulas, this will significantly speed up subsequent worksheet processing you’ll be doing later.  As an alternative, you can use the “Set Array to Values” option in the address verification function of CDXStreamer.   (You can also check out the navigation shortcuts described in our blog article 5 Handy Tips all Excel User Should Know, which can help you move around and select large data sets.)

But how do you find just the data you need, in a very large worksheet?  Here are three ways available to do this efficiently and easily through Excel:

FILTERING:  This is Excel’s version of sorting on steroids.  The filter option allows you to look for items in a list based on lots of different criteria (that apply to both text and numeric values), and then hides the rows that don’t apply.  Select a cell within your data, and in Excel 2007 and up, from the Data tab on the ribbon, click the filter icon:

You’ll notice that drop-down arrows are now visible next to each column of data.  (You should make sure to label each column, so the first row of data is also part of the filtering process.)  Then click on the arrow in the column you’d like to filter, and based on the type of data present, you’ll be presented with text or numeric filter options.  You’ll also be able to do straight-on  sorting as well from here, but we’ll focus here on the filter functions.  For instance, if we just used CDXZipStream to calculate distances from a target destination, we can find all locations within a 50 mile radius by selecting the following options in the custom filter dialog:

A nice bonus is if you copy and paste the filtered data to another worksheet, the hidden rows are not included.  To remove the filter and show all rows of data, just click on the filter icon again.  For more information on filtering in Excel, please refer to the following Microsoft article: Filter Data in a Range or Table.

CONDITIONAL FORMATTING:  You can change the font, fill, and border colors and formatting depending upon the cell value.  From the Home tab, click on Conditional Formatting:

Using the example above, click on Highlight Cells Rules, and specify formatting for values less than 50:

For more information, check out the Microsoft article Add, change, find, or clear conditional formats.  

You can also combine conditional formatting with filtering, by using the “sort by color” option available in each column drop-down menu.  Please refer to the article Filter by cell formatting.

VLOOKUP/HLOOKUP:  Excel VLOOKUP or HLOOKUP are great worksheet functions that can link tables together when they have a common column or row of data.  For this example we’ll use VLOOKUP, which is applied to column-oriented data.  Let’s say we have a list of routes which have all been optimized using CDXZipStream; we would like to list all the destinations with their contact telephone numbers, but the optimization process has changed their order from their original list.  To find the telephone numbers in the original list associated with each destination, we create another column with the formula:

=VLOOKUP( LOOKUP_VALUE, TABLE_ARRAY,COL_INDEX,[RANGE_LOOKUP]) where

LOOKUP_VALUE  =  the destination address (in the optimized list)

TABLE_ARRAY = the range of cells that contains the destination addresses and telephone numbers in the original list.  The first column of this range should contain the destination addresses.

COL_INDEX = the column number of the TABLE_ARRAY that contains the telephone numbers

RANGE_LOOKUP = A logical value, where “FALSE” indicates an exact match is required

If we use a cell address for the first lookup value, we can copy this formula along all the optimized destinations to find their telephone numbers.  Make sure however, that the TABLE_ARRAY range is defined by either a named range, or by using an absolute cell range with dollar signs (e.g $A$1:$D$500).

For a list of optimized destinations in column A, we can search for the corresponding telephone number in the range $H$1:$K$200, where the destination addresses are in column H and the telephone numbers are in column K, with the formula:

=VLOOKUP(A1, $H$1:$K$200,2,FALSE)

Then just copy this all along the list to find all telephone numbers.

For more information, please refer to these articles about VLOOKUP and HLOOKUP.

Sometimes it's particularly important to get accurate distance information between addresses, and when managing large address lists, the fastest and most accurate approach is to first geocode the locations (get their latitude and longitude) and then use this information as the basis of the distance calculations.  CDXZipStream does this particularly well with a combination of two functions, CDXLocateMP and CDXDistance2WP.  We're going to review step-by-step how you can use these two functions within Microsoft Excel, to filter an address list based on the calculated straight-line (as the crow flies) distance to a target address. 

Let's say you have a long customer address list and would like to determine which ones are closest to a specific store location.  In many cases, especially in sparsely populated areas where zip codes may cover hundreds of square miles, it's not appropriate to use the centroid location of the zip codes as the basis of the distance calculations, the approach used in many zip code-to-zip code calculators.  To get accurate address-to-address distances, first geocode all addresses using CDXLocateMP.  The input box would look like this, where Excel cell references are used as the input for each address.

This is a multi-line address where the street, city, state and zip code are listed in cells A2 through D2.  We also request that latitude is returned to the worksheet cell where the cursor is pointed, and here is the result returned to cell E2:

We repeat the process to get the longitude returned to cell F2, by simply specifying longitude as the returned data.  Now all we have to do is copy and paste the formulas in these cells to the rest of the list, and all customer addresses are now geocoded.

After also geocoding the location of the targeted store, we're now ready to calculate the actual distances to the store based on latitude and longitude, using the function CDXDistance2WP.  Since this function uses a straightforward mathematical formula, the calculations are very fast.  Here's an example of the CDXDistance2WP input:

We use cell addresses for the data, in this case the geocoded "waypoint".  For the target location, we also specify an unchanging cell location using the dollar sign ($) before the column letters and row numbers.

To get distance data for the entire list, we again just copy the resulting equation from the first row of data all along the list of geocoded points.  To see a short tutorial showing the use of both CDXLocate and CDXDistance2WP functions, please see the YouTube video Geocoder in Excel.

Now use Excel's sort capabability to sort the distances, or use the autofilter function for more advanced features.  Autofilter can easily show you all the distances that fall within a radius.  Let's say we want to narrow the list of addresses to those within a 50 mile radius of the target address.  In Excel 2007 and 2010, follow this procedure:

1. Use your cursor to select all the data (addresses and distances) you wish to filter.

2. From the Data tab, select Filter.

3. You should now see drop-down buttons at the top of each column of data.  (You should be using column headings here so the buttons don't cover the first row of data.) Click on the drop-down button in the column of distance data.

4. Click on "Number Filters"

5. Select "Less Than or Equal to"

6. Enter the value "50" in the box to the right of "Less than or Equal to"

All rows that do not meet the 50 mile or less requirement are now hidden.  You can now copy and paste the filtered data to a new workbook to save it.

For more information about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

Radius analysis based on zip codes can be an extremely easy way to perform a variety of tasks, such as identifying customers closest to a store location, assigning client calls to sales representatives, or designing a direct-mail campaign for a charitable event.

Our Excel add-in CDXZipStream uses the custom function CDXDistance to calculate the straight-line distance between zip codes.  (Since this is a very fast calculation, thousand of zip codes can be processed very quickly.)  To help our clients better use this functionality, we are now offering a free Microsoft Excel template that employs CDXDistance to automatically filter zip codes based on their distance from a target zip.  

Just enter a list of zip codes in the template, specify the "target" zip code of interest and the radius distance (in either miles or kilometers), and the template automatically filters the list and shows only those zip codes that fall within the radius distance.  Any rows that contain zip codes not within the radius are hidden by Excel's autofilter function. 

There are also several blank columns available for other data associated with the address information in the template, such as name or telephone number.  These fields are filtered along with the provided zip codes and other address data.  After filtering, the results can be copied and pasted to a new Excel workbook; any hidden rows will not be copied to the new workbook.

As an example of how to use the template, we also offer a customized version which can identify nuclear facilities within a radius distance of a specified zip code.  In this case, we have provided (in the data entry area) zip code information for each nuclear facility located in the U.S.  As in the generic template, just enter a target zip code and radius distance, and only the nuclear facilities that fall within the specified radius will be displayed.

Both templates can be evaluated with the free demo version of CDXZipStream, and for longer-term use will work with the CDXZipStream Lite version.