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.

Our Microsoft Excel add-in for zip code and location analysis, CDXZipStream, can generate zip and postal code lists for various geographies like state, county, and city.  But if you need to compare two lists of zip or postal codes from CDXZipstream, you can use the computational power of Excel to do the job.  Here’s a couple of different approaches, depending upon the version of Excel you are running:

VLOOKUP:  The worksheet function VLOOKUP works with Microsoft Excel 2003 and higher versions.  For example, let's say we have two zip codes lists like this:

And we want to find which zip codes are in List 1 but not List 2, and vice versa.  Just to the right of the first zip code in List 1, in cell C3,we input the formula:

=VLOOKUP(B3,E:E, 1, FALSE)

This indicates that we are trying to find the value of cell B3 (ZIP code 95229) in Column E (the location of List 2).  If the value is found, the value in the first column (the Zip code) will be returned; if not, “#N/A” is returned.  The input value of FALSE indicates an exact match must be made.

We copy this formula all along List 1.  We also input a similar formula to the right of List 2 that will search List 1:  

=VLOOKUP(E3,B:B, 1, FALSE)

The final result is:

For long lists you may want to use Excel’s sort function to sort these results and more easily identify where the lists are different.

Previously we’ve also shown how you can use VLOOKUP to compare results of zip code radius analysis; for example, identifying the zip codes where two radius areas intersect.  Please refer to the blog post ZIP Code Radius Analysis for Multiple Areas for more information.

CONDITIONAL FORMATTING: This method comes to us from Chandoo.org, and can be used in Microsoft Excel 2007 and up:  

1. Use the cursor to select the cells in both lists.  (After selecting cells in List 1, hold down the CTRL key and select List 2.)

2. From the Home tab, select Conditional Formatting, then Highlight Cells Rules, then Duplicates.

3. You can select to conditionally format unique or duplicate values, and also select color combinations. 

Here is the result, where we highlight duplicate values:

Again, the data can be sorted (based on cell color here) to more easily group and identify differences between the lists.

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.

We were recently asked by a customer if it is possible to use CDXZipStream, our Microsoft Excel add-in for address and zip code analysis, to determine whether a zip code is in a rural area.  Here’s our response:

A fairly stringent determination of rural zip codes, which can be done easily using CDXZipStream, is based on whether the zip code is part of a Core Based Statistical Area, or CBSA. CBSA’s are defined by the Office of Management and Budget as urban areas; a metropolitan area contains a core urban area of 50,000 or more population, and a micropolitan area contains an urban core of at least 10,000 (but less than 50,000) population. Each metro or micro area consists of one or more counties and includes the counties containing the core urban area, as well as any adjacent counties that have a high degree of social and economic integration (as measured by commuting to work) with the urban core. Based on the 2010 Census, approximately 95% of the U.S. population lives in either a micropolitan or metropolitan CBSA.   If a zip code is not associated with a CBSA then, it is a good assumption that it is in a non-urban, or rural area. Using the CDXZipCode premium feed (available in CDXZipStream Demographic version and higher), request the data field “CBSA”; if the returned value is "N/A", then the zip code is not associated with any CBSA and you can assume that it is rural.

The attached map shows the current coverage of Core Based Statistical Areas in the U.S. and Puerto Rico:

Source: http://www2.census.gov/geo/maps/metroarea/us_wall/Dec_2009/cbsa_us_1209_large.gif

Using an alternative U.S. Census Bureau definition, rural areas comprise open country and settlements with fewer than 2,500 residents.  All other (urban) areas are of two types—urbanized areas and urban clusters—identical in the criteria used to delineate them but different in size. The Census Bureau defines an urbanized area wherever it finds an urban nucleus of 50,000 or more people. They may or may not contain any individual cities of 50,000 or more.  Urban clusters have a population of at least 2,500 but less than 50,000 persons.  Using these criteria, about 16% of the U.S. population lives in what is considered to be rural areas, based on the results of the 2010 Census.  You can download from the Census website relationship files showing which ZCTA’s (the Census Bureau’s approximation of zip codes) correspond to urban and non-urban (rural) areas.

You may also want to consider population density as a rough measurement of urban/rural characteristics, Population density can be easily calculated by dividing the total population of the zip code by the land area.  Again, using the CDXZipCode Premium feed, request the data fields “Population” and “LandArea” and then use Microsoft Excel to perform the calculation.  Keep in mind, however, that population density provides only a partial picture, since there may be non-residential areas with low population counts that exist in the middle of urban centers.  

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

CDXZipStream, our Excel add-in for zip code and location-based analysis, is an intentionally non-subscription, pay-as-you-go software so our clients can decide for themselves how often to purchase new data.  The need for data updates certainly varies depending on what data is used and how it is applied, and ultimately we feel this decision is best left up to our clients.

CDXZipStream updates are available as a one-time purchase and as a one-year subscription, where twelve updates are provided monthly.  Updates apply to CDXZipStream Basic and Premium versions, and to a separate Canadian database of postal codes.  Please see the last portion of our price list for pricing for all update versions.

Our basic and premium data feeds for zip codes are updated with new data every month.  Since zip codes are frequently retired or added by the U.S. Postal Service, this data is most subject to change. Out of about 42,000 zip codes in the U.S., an average of 0.9% of records change per month, and 11.2% of records change per year.  Unemployment data (provided in county, state, and Core Based Statistical Area data feeds) are also added and updated monthly.

All our other data feeds, which are primarily based on U.S. Census Data, are updated less frequently.  Data from the American Community Survey (ACS), which will soon be replacing much of the data from the ten-year census, is updated annually, usually in the last quarter of the following year.  We will be using our newsletter to announce updates to these data feeds as they occur.

When you need to update your database, click on the "License Information and Software Updates" icon on the CDXZipStream toolbar, and select "Data Updates" to login to your account and purchase an update. If you've forgotten your user name or password, please contact us at support@cdxtech.com.  For our clients who have purchased support plans, we send an email notification when monthly updates are available.  For more information please call 1-877-239-8321 or contact us at sales@cdxtech.com.

Once a new update has been purchased, or to obtain a monthly update as part of a subscription plan, you can select the “Product Management” under the My Account area of our website (on the left-side menu), and click on “Download Data Files” to download and install the new database.  Alternatively, you can also click on “Buy Data Updates” here to purchase updates.

So, how often should you get updates?  First, consider how critical accuracy is for your application.  If you’re using CDXZipStream to verify addresses in a direct marketing campaign, and even a small percentage of non-valid addresses can make or break the return on investment, consider getting a year’s subscription so you get new data every month.  For non-critical applications, we generally recommend updates at least every six months.  In fact, you’ll see a reminder when you open CDXZipstream if your data is more than six months old.  (You can disable this feature by clicking on the “License Information and Software Updates” icon on the CDXZipStream Toolbar, and unselecting the “Warn if Zip Code Data Out of Date” checkbox.)

Please note that CDXZipStream functions related to driving and mapping use information from Microsoft MapPoint, and are only as good as the MapPoint version installed on your computer. MapPoint is updated every one or two years and the newest 2011 version does contain updated maps with new geographic data.  If you wish to purchase MapPoint North America 2011 we recommend buying at the reputable reseller MP2KMag.com.

In the past we’ve mentioned customization services for our Microsoft Excel add-in CDXZipStream, and we’d like to take the opportunity here to discuss some examples of how customization may help better support your business needs around address, zip code, and data analysis in general. 

Do a One-Time Analysis:  

Need to use CDXZipStream functionality just once?  If you are not interested in purchasing the software for continuing use, we can provide the hard data that fills your specific need.  This is often an economically attractive alternative, particularly in lieu of the more expensive demographic versions of CDXZipStream.     

Get a Custom Excel Template:  

We can build pre-formatted templates that perform specific tasks around existing CDXZipStream functions, often combining multiple functions from CDXZipStream and Excel to get the final required answer.  Tasks can usually be fully automated so that after data entry, a simple click of a button is all that’s required of the user.  We currently offer free, downloadable templates that perform basic analyses, such as geocoding (getting latitude/longitude data), route optimization, radius analysis, and more.  But if you have a more complex task, consider a customized template.  This is also a great option if you need to standardize CDXZipStream applications for less experienced Excel users.

Please refer to the following tutorials to see our standard templates in action.  We can modify any of these templates to fit your particular needs:

- Route Optimization with One Click

- Driving Distance and Time Calculations in an Excel Template

- Driving Distance and Time for a Matrix of Addresses

- Zip Code Radius Analsysis in an Excel Template

- Get Latitude and Longitude for Addresses in Excel

- Reverse Geocode Template for Microsoft Excel

Apply CDXZipStream Beyond Excel:  

We’ve reviewed in a past blog post the basic programming code that allows CDXZipStream to be applied to other Office applications beyond Excel.  This code is not proprietary and we invite our clients to use it as they please.   Not into programming?  Let us do it for you, and get CDXZipStream functionality, ranging from route optimization to radius analysis, exactly where you need it.

Use CDXZipStream with a Custom DataBase:

We’ve designed CDXZipStream around the use of address, zip code, and demographic data, but the concept of selectively taking data from a local database and putting it in Excel with custom functions can be applied to a wide variety of data needs.  Whether you have a private database you would like to use more efficiently through a spreadsheet platform, or want to mine a commercially available set of data that we don’t currently include in our own feeds, we can build a version of CDXZipStream that will work for you, using our patented interface.  Some types of custom databases include:

- Commercial address or postal code databases for countries other than the U.S. or Canada

- Census data for specialized demographics not now included in CDXZipStream

- Proprietary customer data

- Demographics by other geographies, such as voting districts, school districts, urban growth areas, etc.

For more information about CDXZipStream customization, or to request a free quote, contact us at 1-877-CDX-TEC1 (239-8312) or customsolutions@CDXTech.com.

Address information is a very basic component of running a business, whether you’re defining a marketing plan based on yours customer’s geographic location or routing service calls for your technical team.  Address accuracy is also critical for minimizing marketing, customer service, and delivery costs. Our Microsoft Excel add-in CDXZipStream is great at helping you perform zip code and address analysis, but we want to highlight here another good option which tends to get overlooked:   geocode (latitude and longitude) information as an alternative to text addresses.

You may remember from third grade geography how the idea of using points of latitude and longitude on the globe started a long time ago in ancient Greece.   The use of this “geocode” system was pretty much limited to navigating the seas until the general use of GPS (Global Positioning Systems) via satellite exploded in the marketplace.  Now a Garmin GPS can get you to Grandma’s house on Thanksgiving Day, and your GPS-enabled iPhone can find your morning coffee at the nearest Starbucks.

The great thing about latitude and longitude data, as opposed to a text address, is that it is an unequivocal identification of a specific place on the map.  Streets get renamed, buildings get torn down and renumbered, zip code areas get added or redefined, but latitude and longitude does not change.  Particularly when address validity is questionable, i.e. the zip code doesn’t match the city, the house number doesn’t exist, or the street name is misspelled, you may want to use latitude and longitude instead.

CDXZipStream, working in conjunction with Microsoft MapPoint, can both geocode an address (find its latitude and longitude) and reverse-geocode (find the closest address for a latitude/longitude point).  It can also use latitude and longitude as input to functions like CDXRouteMP and CDXLocateMP.   For example, if we want to calculate the driving distance to a customer location with a questionable street address, we can use CDXRouteMP with latitude and longitude, like this:

This is equivalent to using the worksheet equation:

=CDXRouteMP(0,0,"752 W End Avenue, NY, NY 10025","40.789283|-73.966078")

Latitude and longitude is provided as “40.789283|-73.966078" where the values are separated by a vertical bar "|".  Just remember to use decimal format and negative values for west and south global locations.  All locations in North America will have positive latitude and negative longitude values, and all European locations will have positive latitude and positive longitude values.

To use latitude and longitude for the function CDXLocateMP, input the geocode data as shown below:

In this case, we are requesting street information for the geocoded point, input as a single-line address.  You can also input latitude and longitude separately as a multiline address, in the street and city input boxes, respectively.  Again, this is equivalent to the worksheet formula:

=CDXLocateMP(4,"40.789283|-73.966078",,,,,1)

Do you know the location exists, but the address can’t be found by Google or MapPoint?  You can find the latitude and longitude of the location in question, or a nearby landmark, by right-clicking on the point in Google Maps and selecting the option “What’s here?”  The latitude and longitude values will automatically show up in the Google search box near the top of the screen.  You can also use resources like Itouchmap.com or Microsoft MapPoint, which shows latitude and longitude of the pointer at the lower right-hand area of the displayed map.  Once you have latitude and longitude, input these values into the CDXRouteMP or CDXLocateMP functions.

Latitude and longitude is also very useful for calculating straight-line distance between locations.  You can use the CDXZipStream function CDXLocateMP to find latitude and longitude for a list of addresses, then use CDXDistance2WP to find the distance between all desired points.  This is a very fast, very accurate calculation.  (For a more detailed description of this process, please refer to our prior blog article “How to Very Accurately Filter Addresses Based on Distance“.)  Don’t have detailed address information?  CDXLocateMP can find latitude and longitude based solely on zip code.  You can then use these points to calculate distance using CDXDistance2WP.  In this case, CDXZipStream uses Microsoft MapPoint to find the latitude and longitude of the zip code centroids. The centroid is the weighted geographic center of each zip code area.  

We also provide preformatted Excel templates which can perform geocoding and reverse-geocoding.  Just cut and paste your address or zip code list into the template and get your data at the click of a button.  These are free and can be downloaded from our links page.

For some short tutorials related to using latitude and longitude with CDXZipStream, please see the following videos:

Get Latitude and Longitude for Addresses in Excel 

Reverse Geocode Template for Microsoft Excel 

Gecoder in Excel 

CDXZipStream, our Excel add-in for address, zip code, and route analysis, has a licensing agreement that is significantly different (in a good way!) from most other software.  Understanding that CDXZipStream contains a number of functions for route planning and optimization, it makes sense to allow our users to install it on multiple computers.  For example, you can plan and optimize a route on your desktop, then follow the directions for the optimized route on a laptop in your car.  For very long calculations, it is also helpful to use a secondary desktop or laptop so your main computer is not tied up.  Since the CDXZipStream license is provided per user, you won’t have to buy multiple licenses to do this; each CDXZipStream license comes with three key codes, allowing you to install it on three separate computers.  

To set up CDXZipStream on a new computer simply download and install the CDXZipStream demo on the new computer.  When installation is complete and Microsoft Excel opens, press the third button on the CDXZipStream toolbar named "License Information and Software Updates". (If you are using Excel 2007 or 2010, the CDXZipStream toolbar is located under the "Add-Ins" menu.) Then press the "Buy Now" button followed by pressing "Generate KeyCode" and log into our web site using the username and password you received with your original purchase.  If you did not retain your original purchase information please contact us at support@cdxtech.com

A web page allowing you to generate a new keycode should appear with your new Computer ID automatically entered. This Computer ID is unique for each PC and your registration code is based on it. Then press "Next" to generate your new registration key.

Copy your new keycode from the box that appears and then press the "x" in the upper right corner and minimize your browser. Then paste the copied code into the "Keycode" section of the "CDXZipStream Purchase and Registration" box and press "OK". (This is the box where you pressed the "Generate Keycode" button.) CDXZipStream will now be fully activated on your computer.

Your online account can also be used to purchase data updates or upgrade your product to a more powerful version.  A history of all data purchases is maintained in the "Download Data Files" section.

Our license does not permit sharing with other users on additional machines.  We do require that multiple users purchase multiple licenses.  Each CDXZipStream license will be registered under a person’s name, which can be displayed by clicking on the “License Information and Software Updates” button on the main CDXZipStream toolbar.  Also shown is the number of licenses associated with the name.   If the name needs to be transferred, such as in cases where an employee has left an organization, please contact us at support@ cdxtech.com.  We also offer generous volume discounts of up to 20% for 10 licenses; please contact us for details if you need more than 10 licenses.