If you've ever hesitated with indecision when the dessert cart comes around, we know how you feel.  Choice is a good thing when it comes to the sweet things in life, but sometimes a little help is needed to make the right decision.  We especially believe that having a choice is a good thing when it comes to software, and so we've always offered CDXZipStream in several versions to provide our clients with the functionality that best suits their needs.  Now with eight different versions and a wide (and growing) range of capabilities, we thought it would be a good idea to review in detail the functionality of the most popular versions, as an aid to current and future clients as they purchase and upgrade their software. (Note:  Pricing information for both new purchases and upgrades is available here, with discounts available for multiple licenses.  And remember, all versions of CDXZipStream come with a 30-day money-back guarantee, so don't worry, you can send back that cherry tart after taking a bite ...)

CDXZipStream Lite 

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic). See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

CDXZipStream Basic 

Does everything the Lite version does, and contains additional functionality provided by Microsoft MapPoint.

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic).  See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Basic:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium

Does everything the Basic version does, and contains extensive demographic data.

- Provides basic demographic data by zip code, city, county, area code, CBSA, and state

  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA, CDXState, CDXCensus, and CDXCensus2.   See here for specific data fields for    these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium ACS

Does everything the Premium version does, and contains demographic data from the latest American Community Survey.

- Provides extensive demographic data by zip code, city, county, area code, CBSA, and state, from the American Community Survey, 2000 Census, latest Business Census,    and other sources.  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA,  CDXState, CDXCensus, CDXCensus2, CDXACSCBSA,    CDXACSCounty, CDXACSPlace, and CDXACSState   See here for specific data fields for these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium ACS:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

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.

In our last blog we reviewed all the free Microsoft Excel templates we offer for CDXZipStream, that can help you make tasks like route optimization, zip code radius analaysis,  and geocoding that much easier.  You also have the option to customize the appearance of these templates, particularly to help highlight specific data using Excel built-in functionality.  Let’s take a look at the Zip Code Radius Calculations template as an example.

This particular template includes optional data columns B through E that can be used for information like name, telephone number, or any other fields associated with the listed addresses.  You can fill in the appropriate field names at the top of the data columns, or if you don’t need to use these columns, just hide them.  To hide the columns In Excel 2003, select Format (Column) from the main toolbar, or in Excel 2007 and 2010 use Format (Visibility) from the Home tab.   Do not delete these columns, since this will change the location of the input data required for the analysis, and will make the template inoperable.

We also recommend that to ensure you don’t lose any data along the way, you should save the template under a new name both before and after making any major changes.  

Now let’s review a few methods to change the display of the template data worksheet.  Keep in mind that once these changes are made, you can then copy and paste the results to another blank workbook to permanently save them:

1.  Sort - Most users of Microsoft Excel have used the sort function at some point, and it can certainly be used here if you need to sort the input or output data in the worksheet.  After pressing “Calculate” in the Zip Code Radius Calculations template, the worksheet will already be autofiltered in order to show the addresses or zip codes that fall within the specified radius distance of the target.  But even after autofiltering, the addresses will be shown in the order in which they were entered into the worksheet.  If you would like to the addresses in order of their distance to the target zip code, for example, select Data (Sort) from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Sort from the Data tab.  From here you can sort by the appropriate column in either ascending or descending order.  For more information about sorting, check out these links for Excel 2003 and Excel 2007-2010.

2.  Autofilter – Autofiltering hides any rows that do not meet the filter criteria you specify.  For instance, you may want to show only address data for a particular city, zip, or other data field.  Since the Zip Code Radius Calculations template automatically autofilters the results based on the distance from the target zip code, you will need to first turn off and then turn on the autofilter if you would like to use this for some other filtering criteria.  You can also filter based on two data fields, for example, if you want to show only those addresses within the target radius that are in a single town.  To autofilter  make sure a cell in the white data area is selected, then select Data (Filter - Autofilter) from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Filter from the Data tab.  For more details about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

3.  Conditional Formatting – This approach usually uses color to highlight data of interest.   Just specify the font color, background color, or other format option using similar specifications to autofilter;  in this case however, rows are formatted as specified if they meet your criteria, not hidden if they don’t.   For instance, you can highlight address and/or distance values as desired, and just like in sorting or filtering, this can be done simultaneously for one or more data fields.  To apply conditional formatting, select Format, then Conditional Formatting from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Conditional Formatting from the Home tab.  For more information about conditional formatting, see these links for Excel 2003 or Excel 2007-2010.

However, please remember to retain and make visible the instructions worksheet in the template that includes the Hughes Financial Services copyright.