Like us on FacebookFollow us on Twitter  Find us on Facebook and Twitter





Welcome to the CDXZipStream July 2013 Newsletter


This month we highlight our free on-line tool that allows you to look up demographic and other general data for individual ZIP codes and Canadian postal codes.  We've modified our bulk radius template so you can directly input latitude and longitude points instead of relying solely on address data; the template is being offered at the special price of $149.95 through September 30.  We also cover some important tips about the very useful but sometimes tricky Excel function VLOOKUP, which can help link CDXZipStream and CDXStreamer results to other worksheet data.


Here are this month's blog articles:



Free Demographics for ZIP and Postal Codes


CDX Technologies has a free on-line tool for getting demographics by ZIP code and Canadian postal code. In cases where you need some quick data, just input the code of interest and specify the report you need - general data about the location, demographics, population gender, or race - and a clear and concise report is returned to the web page.  You can also quickly export the data to a Microsoft Excel spreadsheet or presentation-quality PDF file, which is easy to send as an email attachment or include as part of a larger report.  For those times when CDXZipStream doesn't quite fit the bill, consider bookmarking this tool for easy, fast access to demographics and other general data.



Bulk Radius Template with Latitude and Longitude Input


When you need to do radius analysis for a matrix of X by Y locations - we call it bulk radius analysis - consider using our bulk radius template, which can now use either address or latitude/longitude locations as input.  This tool can perform many repetitive distance calculations, and can answer questions like:  With X stores and Y distributions centers, which distribution centers are closest to each store?  Or with X hospitals and Y patients, which patients are closest to each hospital? 


Locations can be sorted based on distance to a target location, and best of all, you can also specify a radius distance and create a complete report, listing all Y locations located within the radius of each X target location.   For a quick tutorial on how to use the template please watch the video Store Locator Analysis in Microsoft Excel.


Now through September 30, 2013, the template is being offered at the special price of $149.95.


The advantage of using latitude and longitude input is that the template does not rely soley on the MapPoint database to map locations, and therefore is faster and more accurate.   We have found that many of our customers already have readily-available GPS data for analysis, so adding the flexibility of using latitude and longitude input just makes sense.  We've also added the ability to stop and start the template to allow for intermediate review of the results, especially useful for working with large data sets.


The template is compatible with any MapPoint, Basic, and premium demographic versions of CDXZipStream. You must first own or purchase a compatible version before you can choose the option of purchasing the template from your account.  Microsoft MapPoint, a separate purchase, is also required. 


Using VLOOKUP to Find Your Data


Many of our clients use the Excel LOOKUP function to link data from CDXZipstream or CDXStreamer to other data residing in their worksheets.  For instance, demographic data from CDXZipstream can be linked via zip code to other customer data covering buying habits, return history, social media participation, etc.  The VLOOKUP formula takes the form: =VLOOKUP(Lookup_value,Table_array,Column_index_number, Range_lookup) where


Lookup_value = The reference value to search for in the first column of the table array.  For the example above, this can be the actual value of the ZIP code,"07869" or its cell address "A1".


Table_array = The range of data of to be searched. The first column on the left of the Table_array must contain the reference values being searched, in this case ZIP codes.


Column_index_number = The column within the Table_array that contains the data that will be returned by the function.  For the example here, this could be demographic data.   If column_index_number = 3, for instance, the data resides in the third column counting from the left.


Range_lookup = True or False.  True looks for an approximate match, and False looks for an exact match.  You should almost always specify False.


VLOOKUP can be a bit tricky to use, however.  Here are three common issues in using this function.


 1.  VLOOKUP will only work when all reference values are similarly formatted.  CDXZipStream always returns data as text, so an easy approach is to force all ZIP code reference values into text by using the Excel TEXT function.   For example, ZIP codes can be converted to text by using the formula =TEXT(A1,"00000"), where A1 is the first cell location of ZIPs.  You can then copy this formula along any list of ZIP codes and use the results as reference values.  Note that simply formatting data as text using cell formatting will unfortunately NOT usually work for VLOOKUP.


2. The data you're searching should not contain any leading or trailing spaces, or non-printing characters.  Use the Excel functions TRIM and CLEAN to remove unwanted spaces and non-printing characters, respectively. 


3.  Use absolute referencing, or a named range, to define your table.  If the table array is defined with relative referencing, like A1:D100, it will change as you copy it along your list.  To avoid this use absolute referencing ($A$1:$D$100), or use the Excel's Name Manager (on the Formulas tab) to give a name to your table array.


For more information, please refer to the following blog article showing an example of using CDXZipStream with VLOOKUP:  ZIP Code Radius Analysis for Multiple Areas.   

Latest Data Updates


Canadian and U.S. data feeds were updated as of July 12.  There were 78 changes made to the U.S. database this month.  If you would like to update your database to reflect these changes, click on the "License Information and Software Updates" icon on the CDXZipStream toolbar, and select "Data Updates" to login to your account.


We hope you find the information here helpful for you and your organization.  Please contact us with your feedback and suggestions by replying to this email.


The Team at CDX Technologies 

July 2013



Map of the World


In This Issue

Free Demographics for ZIP and Postal Codes


Bulk Radius Template with Latitude and Longitude Input


Using VLOOKUP to Find Your Data 


 Latest Data Updates






CDX Demographic Data (On-line Tool)


You Tube Video: Store Locator Analysis in Microsoft Excel





CDX Technologies | 2 West Hanover Avenue | Suite 212 | Randolph | NJ | 07869