We have often been asked if CDXZipStream can apportion a large number of destinations into individual routes, then optimize the order of stops along each of these routes.  Unfortunately the route optimization function of CDXZipStream, which relies on the use of Microsoft MapPoint, is not sophisticated enough to do this kind of calculation.  The vast number of permutations that could occur here requires a lot of computational muscle, and if there are rules (such as geographic constraints) that must be followed in apportioning the destinations, these must be programmed into the selection process as well.

But this is also a case where the human eye and a little brain power, with some help from Microsoft MapPoint, can compete pretty well with even the more sophisticated (and expensive) software.  The trick is to be able to quickly and easily visualize and manage the data, which is right up MapPoint’s alley.   So let’s get started.

Let’s say we have a list of 100 destinations, which we need to divide into 10 routes (of about 10 destinations each), followed by optimization of each route.     First we import all the destinations into MapPoint using the data import wizard available from the “Data” menu on the MapPoint toolbar:

From here you can then select the Excel file that contains the destination list; you can also select other file types here such as text or Access database files.  After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:

You can also choose to import data beyond just address information, which may be useful if this data is part of the decision-making process in apportioning routes.  (You can display all information associated with a destination by right-clicking on it later on the MapPoint map.)  Click on “Finish”, then select the pushpin map type in the next dialog:

Once the data is displayed as pushpins in MapPoint, you can use MapPoint drawing tools to apportion the data into routes.  The freeform tool was used to draw the area below:

You may also want to try the radius tool so you can specify the radius distance of the area.  Once an area has been drawn, right-click on the area boundary line and select “Import to Excel”.  You’ll immediately see all the points within the area in an Excel spreadsheet, ready to optimize with the CDXZipStream function CDXRouteMP.  You can also cut and paste these points into our Route Optimization template, available for free download from our links page.

This technique may not be appropriate in cases where there are many criteria for apportioning routes.  In the case above, we used logical boundaries to define the route area, such major highways and the river to the northwest.  (This is very easy for us humans to do, but can be pretty complicated for a computer.)   As mentioned previously, you can also import other data into the spreadsheet which can be used as criteria in apportioning.   This data can be displayed by right-clicking on a destination on the map, or even better, can be used to define the appearance of the destinations when selecting the map type, by selecting options such as color-coded pushpins or sized or shaded areas.  

Keep in mind that before optimizing each defined route, you may need to modify the beginning and end point destinations.  CDXZipStream assumes these points are fixed and only optimizes the order of the intermediate points.   In the majority of cases this assumption works, but you may also want to add one or more addresses that represent the true beginning and end points if they fall outside the area you just defined in MapPoint.

For more information on using CDXStreamer for route optimization, please watch the following tutorials:

Route Optimization in Excel

Route Optimization with One Click

A major priority for CDX Technologies is to provide excellent support for all of our software.  We find that first-class support not only (obviously) improves our customers experience with our products, but also gives us the opportunity to learn about the myriad ways our software is used and how we can improve it.

But the ever-changing world of PC’s can provide a real challenge for developers, and problems can and do arise with even the most carefully designed software.  If you start having a problem with CDXZipStream, we’d like to provide some advice here on how to proceed.

First, check the product support articles on the website.  They cover the most common problems, including:

• CDXZipStream Toolbar Location in Excel 2007/2010

• Filtering Addresses with Radius Distance Returns #NA for all entries

• CDXZipStream Custom Functions Return #NAME?

• Error 1303 During CDXZipStream Installation

• CDXZipStream Toolbar Not Visible

• CDXZipStream Returns "Address is Ambiguous or Invalid" Error

Another common issue is how to reinstall CDXZipStream on a new computer, which is covered in our blog article The CDXZipStream Licensing Agreement. 

If the resources above don’t quickly solve the problem, send us an email at support@CDXTech.com or call 973-895-5542.   It always helps to describe the problem in as much detail as possible, including 

- A description of what you were attempting to do at the time the problem occurred

- The text of any error message (or a screen shot attached to an email if convenient)

- Your PC and software configuration

If appropriate, it can also be extremely helpful to email us your Excel file or a screenshot of the worksheet showing the problem.  This can really speed up the process of troubleshooting issues associated with using CDXZipStream worksheet functions, since the nature of the input data plays an important role in how well the functions work.   In this case, if you must send sensitive or proprietary information, please do not use regular email; you can contact us for access to our secure server.

Many of our clients use the CDXLocateMP function of CDXZipStream (available in the MapPoint, Basic, and Premium demographic versions) to look up the zip or postal code for an address, or to verify that a given code is correct for an address.   Postal code format varies from country to country, and the amount of postal code information available from CDXZipStream and MapPoint varies as well. 

For a detailed description on how to get missing ZIP or postal codes, see our post Getting Zip Codes for an Address List. 

For a short tutorial on how to use the CDXLocateMP function,  please watch this YouTube video "Address Validation in Excel".  

All industrialized countries use postal codes; in the United States we happen to call them ZIP (Zoning Improvement Plan) codes, or ZIP+4 when an additional 4-digit identifier is used.  However, ZIP codes are unique to the United States, and there is not a universal standard among countries.  For example, both Canada and the United Kingdom use alphanumeric codes, which allow for more variation with fewer characters.   

Since the CDXLocateMP function uses MapPoint as its data source, and MapPoint is a stand-alone desktop software, there are limitations on how much postal code data is available for retrieval.  For instance, there are currently over 800,000 Canadian postal codes alone, and it is just not feasible for MapPoint North America or Europe to include them all.  As a result, requesting a postal code for a given address through CDXLocateMP will in some cases (such as Canada) return only a partial code.   Note that for the United States, with only about 40,000 ZIP codes (not including ZIP+4), all five-character codes are included in the MapPoint database and can be returned through CDXLocateMP.

Here is a table showing examples of the postal code format and the data returned for various countries (where MapPoint uses address find):

There are only two instances, Canada and the United Kingdom (both of which use alphanumeric codes) where the entire postal code is not returned from MapPoint.  For example, for the Canadian address that contains the code “L4M 3A5” only the first three characters are returned.  Note that CDXZipStream does have available for purchase a complete database of Canadian postal codes, although this database is not compatible with the MapPoint find address option.  Please see our blog Canadian Postal Code Database - A New Addition to CDXZipStream for more information on use of the Canadian database option.

Remember that when inputting address data in MapPoint or CDXZipStream, it is important to follow standard address conventions which do vary slightly from country to country, as shown in the table above.  In the U.S., Canada, and the U.K. the postal code is listed just prior to the country, while in most European countries the postal code is listed prior to the city.

MapPoint 2013 for North America was introduced in July 2012 and at the time of this writing can be purchased through the Microsoft website for $299.99.  Microsoft doesn’t offer just data updates for MapPoint, so you need to repurchase the software to get the new data too.  If you already own a previous version, is the newest version worth the cost?

Although the new MapPoint has a somewhat new look and feel, there really aren’t any substantive changes in functionality over MapPoint 2011, and so the main criterium for upgrading is the new data it contains.  Here’s a comparison of what you get for recent versions:

The addition of over 200,000 miles of navigable roads is a significant increase over the MapPoint 2010 version, and if you use CDXZipStream and MapPoint for critical routing calculations involving deliveries, cost analyses, or route optimization, you should strongly consider upgrading to MapPoint 2013.   This will also be somewhat dependent upon the area of coverage, since new road construction is less likely in developed urban areas;  issues such as this can only be considered on a case by case basis.

“Points of interest” include restaurants, airports, casinos, etc.  (A new feature of MapPoint 2013 is that it also contains hotel and restaurant reviews.)     Points of interest are not currently incorporated in any CDXZipStream functions, so if you use MapPoint only for its support of CDXZipStream , they should not be a factor in your decision.

We do recommend that you download the trial version of MapPoint 2013 and try it before you buy.  Run some typical routing scenarios and calculations and see if there are any significant differences.  Just make sure that you do not uninstall any previous versions during set-up, and if you decide not to buy at the end of the trial period, run a “Change/Repair” (using the Programs option in your Control Panel) on your previous MapPoint version to ensure it will continue to function properly.  Note that the trial period for MapPoint 2013 is 14 days, where for previous versions it was 60 days.  

One piece of bad news with the new release:  MapPoint 2013 is still provided only in a 32-bit version.  It can be installed on 64-bit Windows operating systems, but is only compatible with 32-bit Microsoft Office (and Excel).  As a result, any add-ins that work with MapPoint must also be 32-bit.   This includes CDXZipStream as well as the MapPoint Office com add-in that allows direct access to MapPoint from within Office.   Microsoft provides both 32 and 64-bit versions as part of the license for Office 2010, so you can pick and choose the version you want to run, although you can't install both versions on the same machine. Alternatively, you can also install an older version of Excel (2007 or 2003) on the same computer with Office 2010 64-bit and successfully run 32-bit MapPoint 2013 and CDXZipStream.  

CDXZipStream,our Microsoft Excel add-in, contains an array of demographic data that can provide invaluable insight into your current and potential customers.  For example, data such as household size, income, educational attainment, ethnic background, and housing value can be a critical input to your marketing program or business plan.  CDXZipStream demographic versions can provide these fields and more, based on resources such as the 2010 Census, the latest 5-year American Community Survey, and the Census Survey of U.S. Businesses   – please see our version comparison for more information.

Once you get the demographics you need from CDXZipStream, it may also be helpful to map the data to get a visual picture of the results.  In this case we recommend using Microsoft MapPoint, which allows you to easily import data from Excel and provides a wide range of mapping options as well.  

(Note:  MapPoint is available as a 14-day, fully-functional free trial, available for download from the Microsoft website.)

To map your data using MapPoint, click on “Data” on the main MapPoint toolbar at the top of the screen, and then select “Import data wizard …” from the drop-down list.  From here you can select the Excel file that contains the demographic data of interest; you can also select other types here such as text or Access database files.  After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:

From here the mapping parameters can be specified.  First select the applicable country or region, as well as whether the first row contains column headings.   Under each column shown in the dialog, you will also need to specify the data type for each column of data.  For example, the first column in the screenshot above is the list of zip codes that forms the basis of the demographic data, and so select “ZIP Code” from the drop-down list of data types. At least one column of data like this must be available in order to geo-locate the data on the map.  For the other columns of data, you can specify “Name” (to help identify the area or pushpin on the map), “Other Data” (which will provide the numeric value), or “Skip Column” (to exclude this data altogether).  Note that if multiple columns of data are identified as “Other Data” all values will be used in the mapping analysis.  Since data will overlap for the same location, these maps may be difficult to read; consider creating separate maps for each column of data as an alternative.

In the example above we want to map median age by zip code.  The first column of zip codes is used to geo-locate the data, so we select “ZIP Code” as the data type, and we also select median age as the “Other Data” type.  The column containing store number information can be used as the “Name” type so this data can be used to identify each point provided on the map.  All other columns of data are specified as “Skip Column”.  After clicking on “Finish”, all we need to do is specify the map type:

To create a map showing circles that are sized relative to the median age value, we click on “Sized Circle”, and the result is:

Passing the cursor over one of the circles will show the zip code and median age value.  Double clicking on the same circle will also show the store number value. 

Once the map has been created, you can also use MapPoint right-click features for each point.   Use your mouse to right-click on one of the circles to show or hide data, include the point in a route, or even create a drive-time zone around the point, like this: 

MapPoint can be an extremely powerful tool to help visualize your geographic data and support your business needs.  Consider evaluating the free trial of MapPoint the next time your get demographic data from CDXZipStream.  We also recommend MP2K as a reputable reseller of MapPoint software. 

In cases where address data is missing zip codes, you can use our Microsoft Excel add-in, CDXZipStream, to quickly and easily get them for you, even for long lists of addresses.  Here’s how:

You will need to use either the CDXZipstream MapPoint or Basic versions, which work in conjunction with Microsoft MapPoint to verify the address location and determine the correct the zip code.  Just right-click on any worksheet cell in Microsoft Excel, and select the CDXZipStream function called CDXLocateMP.  

Address information can be input as a single string (single-line address), or separately as street, city, and state (multi-line address).  In the example, above, we’ve input a single text string as the address.  You can also input the worksheet cell location of the address (e.g. A1), since CDXZipStream automatically recognizes that the value of the worksheet cell is the address string.  Similarly, for a multi-line address, you can input cell locations for each street, city, and state component.

After selecting zip (Postal code) as the returned data, and clicking OK, a formula is placed in the worksheet:

= CDXLocate(7, “501 Cooper Landing Rd, Cherry Hill, NJ”)

Where the “7” parameter indicates that zip code data is being requested.  This cell displays a value of 08002 as the correct zip code.

If you have a long list of addresses that require matching zip codes, make sure you input address data using the worksheet cell locations, so that copying the formula to the rest of the list will result in zip codes being returned for all addresses.  In this case the first formula would be something like this:

= CDXLocate(7, “A1”)

Where A1 is the cell location of the first address.   Similarly, if street, city, and state information are located in cells A1, B1, and C1, then the formula would be:

= CDXLocate(7, “A1”,”B1”,”C1”)

When copying and pasting the first formula (or using Excel’s autofill feature), Excel will automatically adjust the cell references so that the formulas are correct for all rows of the list.

Note that MapPoint does not use additional address data such as suite or apartment numbers in its analysis.  If suite or apartment numbers are part of the address, the address should be formatted so this information is provided after the street number.  For example, "501 Cooper Landing Rd, Apartment 2A, Cherry Hill, NJ" is acceptable.

Since MapPoint is programmatically looking for the best match for each listed address to one in its own database, it’s important to know the quality of the match that was found.  You can do this by using CDXLocateMP to return the best match address found.  Select “best match” as the output, and then use Excel worksheet formulas to compare the result to the original.    

The best match for this address was returned to column C.  (The actual CDXLocateMP formula is shown in the second row).  In column D, a formula was used to check the match with the original address in column A; if a mismatch was found, a value of 1 would be returned; an exact match would return a 0, as shown.  Note that since the zip code was missing in the original address, the found zip code in column B needed to be appended to the original address to perform the check.

You can also use our free geocoding template to quickly determine the type of match found by MapPoint.  (All our templates can be downloaded from our links page.)  Just copy and paste your address list into the template (zip codes are optional), and click on the “Get Coordinates” control button.  The template is designed to return latitude and longitude data for each address, and the type of address match found by MapPoint is also returned.   Here are the possible match types: 

1. Exact - A unique entry was found in MapPoint for this address.

2. Allow Ambiguous-  The first of at least two matching entries was found in MapPoint.

3. Best Match - MapPoint did not find a good match, but this is the best of possible alternatives.

4. Zip Code - MapPoint could not find any matches, so the centroid of the zip code is returned from CDXZipStream

Any address that has a Best Match or Zip Code match type should be double-checked for accuracy.

For addresses in Canada, MapPoint cannot return an entire postal.  There are over 850,000 Canadian postal codes as compared to about 43,000 U.S. zip codes, and only the first three digits of the Canadian postal code (called the FSA, or Forward Sortation Area) are available using the CDXRouteMP function working with MapPoint.  For example,  the CDXLocateMP formula (which includes country data):

=CDXLocateMP(7, “1385 Bank Street, Suite 203, Ottawa, Canada”)

Will return “K1H” as the FSA, although the full six digit code is “K1H 8N4”.  Note that the first letter of an FSA code corresponds to a particular "postal district", which, outside of Quebec and Ontario, covers an entire province or territory.

For more information about the CDXLocateMP function, please also refer to our video tutorial Address Validation in Excel.

There are currently eight versions of CDXZipStream, our Microsoft Excel add-in for zip code, address, and route analysis.  Due to the broad spectrum of functionality of CDXZipStream, we’ve purposely created these versions to allow our customers to pick and choose the one that best fits their needs.  With the release of CDXZipStream 11.0 late last year we’d like to provide in today’s blog an updated review of the functionality of each version, to help you decide which one is right for you.  You can also refer to this reference table for a shorthand comparison.

Note:  We also offer a Canadian postal code database which is compatible with most CDXZipStream functionality.  Please refer to this article for more information about accessing Canadian data with CDXZipstream, or our FAQ page.)

Here are the eight versions and what they can do, along with relevant video tutorials:

FindZip:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)

Reference:  Zip Code Finder in Excel

Lite:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation) as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip code, and find the closest zip code to a target zip. 

Reference:  Find Zip Codes in a Radius

MapPoint:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation), but also has all the mapping and routing functions provided by Microsoft MapPoint, including creation of radius, location, drive time, and route maps, calculating driving time and distance, and route optimization.  It can also geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between two points of latitude and longitude.  In general, this CDXZipStream version is best suited for those requiring driving distance calculations, route optimization, and/or latitude and longitude determination.  Please note that CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel

Basic:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  CDXZipStream Basic is primarily used by those who require both extensive zip code functionality as well as driving and routing calculations supplied by MapPoint.

CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel, Geocoder in Excel

The next four versions also include demographic data.  For a detailed description of the data available in these versions, please refer to data fields information.

Population:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Population counts from the 2000 Census and current population estimates are also covered.

Demographics:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.

Reference:  Demographic Data in Excel

Premium:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Premium ACS:  Contains all the functionality and data of the Premium version, plus demographic data from the American Community Survey.  Covers basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.  Finally, 5-year aggregated data from the annual American Community Survey is provided in data feeds by city (place), county, state, and Core Based Statistical Area (CBSA).   Note that the ACS is administered by the U.S. Census Bureau, and replaces the old long form that was phased out after the 2000 Census.  It contains earnings data and other social and economic information that are not currently covered by the 10-year census. 

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Census Records in Microsoft Excel

Available from our links webpage (under Technical Articles) is an example spreadsheet showing how to use CDXZipStream geocoding and distance functions in an analysis of car dealership locations in the United States.  To download, just click on the link "CDXZipStream - Closing Chrysler Dealers Geocoding Analysis and Distance Matrix".  Let’s review how this works, and how you may be able to use this example in support of your own organization.

In this case there are over 700 dealerships, with locations throughout the United States, which are being considered for closing.  As part of this consideration it is necessary to calculate their relative proximity to each other.  Although it would be possible to calculate the driving distance between each location using the CDXZipStream function CDXRouteMP, a simpler and faster calculation is straight-line distance, based on the latitude and longitude of each dealership.  Since the additional accuracy of driving distance is not necessary for this analysis, we will be using straight-line distance here.

The first step, as shown in the worksheet “Dealers Closing” is to find the latitude and longitude of each dealer, either based on the exact address or by zip code.  The function CDXLocateMP is used to find the latitude and longitude in each case, such as in the following formula in columns I and J, for the first row of data:

=CDXLocateMP(1,D2,E2,F2,G2,"US")

Where

CDXLocateMP is the CDXZipStream custom function formula that finds latitude and longitude

1 indicates that latitude will be calculated and returned to the worksheet; 2 is used for longitude

D2, E2, F2, G2 are the cell addresses of the street, city, state and zip code of each dealership

“US” is the country

Since cell addresses are used in this equation, the equation can be copied down the list of dealerships to obtain latitude for all locations in all rows.  

In the first row of columns K and L the formula uses zip code only:

=CDXLocateMP(1,G2)

This is also copied down the enter list of dealerships.  In cases where MapPoint cannot locate the exact dealership address, only the zip code is used where the centroid of the zip code area is the basis for the geocoding calculation.

Columns M and N uses the Excel IF and ISNUMBER functions to select the latitude and longitude of the zip code centroid when the exact address is missing:

=IF(ISNUMBER(I2),I2,K2)

In the next worksheet, called Dealer Distance Matrix, the location and latitude and longitude data (from columns M and N) of the previous worksheet are placed into a matrix to calculate the distance between all combinations of dealerships.  Straight-line distance is calculated in this matrix using the function CDXDistance2WP.  In the uppermost left hand corner of the matrix, the following formula is used:

=CDXDistance2WP($C5,$D5,E$3,E$4)

Where

CDXDistance2WP is the CDXZipStream custom function formula that calculates distance between two sets of latitude and longitude

$C5, $D5, E$3, and E$4 are the cell addresses of the latitude and longitude pairs

The dollar sign before the column letter of row number in this equation indicates that this value does not change when the formula is copied to other areas of the worksheet.  The 789 x 789 matrix contains the distance in miles between all dealerships, across the range E5 through ADM793.  Due to the number of columns required for this example, Excel 2007 or 2010 must be used.  Excel 2003 can be used for matrices that have 256 columns or less.

The distance data ican now be effectively displayed in the worksheet “Closest Dealers” , showing dealerships according to their distance from a select dealer location.  Just use the drop down box at the top left hand side of the sheet to select a location, and all other dealerships are sorted and displayed by distance using Visual Basic for Applications code.  (Make sure that macros are enabled for this workbook, by clicking on the Macro Security icon from the Developer tab on the Excel ribbon, then select Enable All Macros.)  The VBA  code is accessible by clicking on the Visual Basic icon from the Developer tab and can be customized to suit your particular application, whether it be finding the closest competitor stores, assigning sales representatives, or targeting distribution locations by customer.

For more information about this type of location analysis, please see the following:

Geocoder in Excel (Video tutorial for the CDXLocateMP function)

How to Very Accurately Filter Addresses Based on Distance (CDX Technologies blog)

Our Microsoft Excel add-in for zip code and location-based analysis, CDXZipStream, is available for download in a free 30-day demo version that allows our clients to “try before you buy”.  We want our clients to have a good understanding of what our software can (and sometimes, can’t) do to help their organizations better connect with their own clients and customers; although there are some limitations of the demo, we’ve carefully designed it to effectively show off the features and functionality of the full-version software.

For example, all features of all CDXZipstream versions are enabled in the demo.  This includes all data feeds and all right-click functions, such as functions that perform radius analysis, route optimization, geocoding and reverse geocoding, and zip code list generation.  

There are general calculation limits of the demo, however, of 1000 per function per Excel session.  This applies for both custom functions that grab demographic data (such as CDXZipCode) and right-click functions such as CDXDistance and CDXRouteMP.  The CDXRadius function is limited to analyses of 50 miles or less, and the CDXZiplist function is limited to 1000 returned values.  For the data feeds, the premium demographic versions are limited to four select zip codes, and for those selected zips, the corresponding CBSA, county, and state.

The CDXZipStream demo version is of course compatible with the 60-day free trial version of MapPoint (North American or Europe), and can be evaluated within our free downloadable templates for performing radius analysis, route optimization, geocoding, reverse geocoding, and driving distance calculations.

Also worth noting: the CDXZipStream demo does not have any unwanted add-ons that load to your computer.  We’ve noticed lately a trend among “free” software that will do anything from hijacking your browser to installing new media players.  It’s all probably spelled out in the fine print, but at the end of the day this is an unethical practice we refuse to participate in. 

And finally, after trying the demo and purchasing the version of your choice, we also offer an unconditional, 30-day return policy.  We truly want our clients to consider CDXZipStream as a high-benefit, no-risk software that can support your organization’s needs around zip code and location-based analysis. So please “try before you buy” CDXZipStream in demo form– it really is cost (and risk) – free.

The CDXZipStream toolbar looks a little different now with the addition of the Application Settings icon that’s located fourth from the left, as shown below: 

Click on the icon and you’ll see the dialog box below:   

The “CDXZipStream Settings” control shown here provides access to settings associated with the main CDXZipStream dialog, used for obtaining demographic information.  These are also still accessible by choosing “Settings” on the main dialog.  The “Route Settings” control accesses settings for the CDXRouteMP function, for performing route calculations and optimization.  Route settings are also accessible from the CDXRouteMP dialog.  Note that although these controls both duplicate capabilities available outside the CDXZipStream toolbar, a number of our clients had requested an additional, centrally located approach for modifying these settings, and this is the end result.

The “MapPoint Version” setting is a new feature that supports the ability to toggle between versions of MapPoint.  If you have installed both MapPoint North America and Europe on your computer, you can now choose which version will be applied when performing worksheet calculations with CDXZipStream functions CDXRouteMP and CDXLocateMP.  Choosing “Default” will default to the last version used by CDXZipStream.  If you have only one version of MapPoint installed, you don’t need to set this; the program automatically will look for the current version.  If you don’t use MapPoint, this feature will not be visible.  

Keep in mind that if you're performing an analysis for locations in Europe, and the MapPoint setting is North America, CDXZipStream will not work.  If you find that "Data Item Not Found" is being returned consistently for the locations in your worksheet, please check that the correct MapPoint version is selected.