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

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

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

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

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

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

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

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

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

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

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

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

LOOKUP_VALUE  =  the destination address (in the optimized list)

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

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

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

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

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

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

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

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

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.

An Excel array can be a powerful tool when dealing with a larger number of calculations, and can be especially well-suited to handling the large amount of data that gets imported into Excel by our products CDXZipStream and CDXStreamer.  Excel arrays can be used, for instance, to identify zip codes within a radius area (using the CDXRadius function  in CDXZipStream), and are especially important when performing address verification or correction in CDXStreamer.

We always recommend that arrays be used when working with large address lists, because all the data can be effectively handled through arrays acting as a bulk data call, instead of individual data calls for each address or zip code entry.  The end result is a much faster response time during the data import process.  When using CDXStreamer, you can make sure that arrays are used by getting data via the standard dialog that opens when performing address verification, then using the “Autocopy” option which applies the array to all contiguous items in your address list.  

Alternatively, it is possible to enter or return a non-array formula, then manually copy this formula down an address list, but this constitutes a new data call for each item and will take a much longer time to return all the data.

Another advantage of using arrays is that formulas within the array will dynamically update the returned data whenever changes are made.  For example, you can update your address list with new entries or changes in the existing data, and the array formulas will automatically update to reflect these changes; no second data call needs to be done by the user.

If you’re not familiar with Excel arrays, they can easily be identified by curly brackets { and } around each worksheet formula.  For arrays that cover a range of cells, those cells act as a unit and cannot be modified in part; you must delete or change the entire array at one time.

Keep in mind, however, that very large arrays can slow down Excel just due to the sheer number of calculations being performed every time a change is made in a worksheet.  You do have a few easy options to avoid this problem.  First, you can specify “Set array to values” in the address verification dialog, and CDXStreamer will automatically remove all array formulas and show only the final values in the worksheet.  You can also do this manually, by copying the entire array and pasting values to the same location.  And finally, if recalculation is not necessary for your data, you can also disable automatic workbook calculation (in Excel 2007 from the File tab, select Options, then Formulas, and deselect Automatic under Workbook Calculation).   Changing the arrays to values has the added advantage that you can modify or delete part of the returned data, which the original array does not allow.  However, please note that all of options mentioned here will remove dynamic data updating.

Arrays work well for software add-ins such as CDXStreamer and CDXZipStream, but arrays are also excellent tools for the end user regardless of the application.  If you know how to use formulas in Excel, you can easily supercharge those formulas by using either single-cell or multi-cell arrays.  To get started using arrays, please refer to the following Microsoft articles:

Introducing array formulas in Excel

More arrays: Introducing array constants in Excel

See also our blog posting about how to apply array formulas when using the CDXZipStream functions CDXRadius and CDXZipList:

Applying CDXZipStream Arrays to Large Data Sets

Our zip code and location analysis add-in for Microsoft Excel, CDXZipStream, can be combined with the computational power of Excel to do a variety of interesting things.  For instance, we’ve just recently introduced an easy-to-use bulk radius analysis template for Excel that can take a matrix of X and Y locations (these could be stores, customers, warehouses, delivery points, etc.) and create a comprehensive report showing which X locations are closest to all Y locations, or vice versa. 

Let’s say we have a list of customer addresses and would like to determine which store locations are within a 10 miles radius of each customer.  This could be used to support, for example, a direct mail campaign to alert customers to the closest stores in their area.  But in cases where there may thousands of customers and thousands of stores, determining the closest stores for every single customer is not a trivial calculation.

So how does the template work?  First we copy and paste all store and customer addresses into the Excel template where they can be verified using the address verification function of CDXZipStream (MapPoint version).  Just click a button in the template, and CDXZipStream automatically determines if each address can be matched to a location in Microsoft MapPoint running in the background.  If a match can be found,  CDXZipStream geocodes the location to find its latitude and longitude.   These points of latitude and longitude are used to calculate straight-line distances between all combinations of stores and customers.  So to obtain a report showing the closest stores within radius of each customer, all we need to do is specify the radius distance (in this case, 10 miles) , click one more button, and a listing of all customers, along with the store locations within 10 miles, is automatically created in a worksheet.

The template can work with either full addresses (street, city, state, zip code) or zip codes alone if full addresses are not available.  The user can also bypass the address verification step by inputting latitude and longitude data directly, if it is available from a gps or other source.  And if you need to do the reverse calculation, i.e. find the closest customers for all store locations, just reverse the order of the data input.

For a short tutorial about the template, please watch the YouTube Video Store Locator Analysis in Microsoft Excel.  For more information, contact us at sales@cdxtech.com or 1-877-CDX-TEC1 (1-877-239-8321).  For current CDXZipStream clients, you can login to your on-line account and select "Buy Data Updates" from the Product Management Area.  Select the Bulk Radius Template from the drop-down list and proceed through the purchasing process.  You must own the CDXZipStream MapPoint, Basic, or one of the premium demographic versions to see this as an option.

Since more and more clients are upgrading to 64-bit Windows operating systems,  we’d like to discuss in a little more detail the issues surrounding the use of our Microsoft Excel add-in, CDXZipStream, in the 64-bit environment.

CDXZipstream can be run in 64-bit Windows, but is not currently compatible with 64-bit Office/Excel.  It is only compatible with 32-bit Excel.  When you install Office, you do have the option of installing either the 32 or 64-bit versions since both are included in any download or disc.  In fact Microsoft generally recommends the 32-bit installation due to compatibility concerns with third-party applications such as CDXZipStream.  (Microsoft has quite a few applications that are 32-bit as well.)  If you’ve already installed 64-bit Office/Excel and would like to install 32-bit, here is what you should do:

- Go to the Control Panel, and select Programs – uninstall a program

- Right-click on Microsoft Office 2010 or Excel 2010 (if not part of the Office suite) and select Uninstall

- Follow the prompts to uninstall

- When uninstallation is complete, reboot your computer and install the 32-bit version

Microsoft Windows and Office products that are available in both 32-bit and 64-bit versions are installed with the same product key and use the same license.  Uninstalling the 64-bit version and installing the 32-bit version will have no impact on your licensing, and you will not lose your existing files or data.  

It may feel a bit odd not going with the more powerful 64-bit Office experience, but remember that the main selling point of 64-bit Excel is its ability to handle enormously large files that use in the neighborhood of 2 GB of RAM (this is memory, not just file size).  Since the overwhelming majority of Excel users will not require that amount of memory (our unscientific guestimate is that covers about 99.9% of us), you probably shouldn’t feel like you’re missing out on the advantages of the latest technology.   Also, the 64-bit version does not support ActiveX controls used in many Office solutions, or 32-bit ODBC drivers.

Unfortunately 64-bit and 32-bit versions of Excel 2010 cannot be run on the same computer.  However, you can install older versions of Excel (such as 2003 or 2007) as 32-bit and run  64-bit Excel 2010 version at the same time.  This may also come in handy if you’re nostalgic for the older (non-ribbon) Excel menu, available with Excel 2003, although keep in mind if you work with large files that Excel 2003 contains fewer rows and columns than more recent versions.

Note:  At the time of this writing Office 2013 has not yet become available for purchase, but from information available through Microsoft support, the statements above will continue to hold true. Microsoft still recommends the 32-bit version of Office 2013, because it helps prevent compatibility issues with most other applications, especially third-party add-ins.  You can, however, install on a single PC both the 64-bit version of Excel 2013 and 32-bit version of Excel 2010, the latter being compatible with CDXZipStream.

Using our Microsoft Excel add-in, CDXZipStream, it’s easy to find the zip codes within a radius distance of a target zip.  Just right-click on a worksheet cell, and using the function CDXRadius, input the target zip code and specified radius distance;  an array or text string listing the zip codes within the radius will be returned to the worksheet.  However, this only applies to a straight-line distance around the target.  What if you need zip codes within a specified driving distance or time?

CDXZipStream does not have a special function for this type of calculation, but you can use a combination of two functions, CDXRadius and CDXRouteMP, to easily get the desired result.  For example, let’s say we’re looking for the zip codes within a 20-mile driving distance of zip code 07869.  (We can also do this for driving time as well – we’ll talk about that case later in this article.)  The first step is to find the zip codes within a somewhat larger straight-line radius, using CDXRadius  For this example we’ll use 25 miles.  This will capture all the zip codes within the 20-mile driving radius, and then some.  So the input for CDXRadius is:

Once we have the returned list of zip codes from CDXRadius, we’ll use CDXRouteMP to calculate the driving distance between each of these zips and our target zip.  Again, we right-click on the worksheet, select CDXRouteMP, and use this following input to calculate the driving distance to 07869:

Note that the first returned zip code from CDXRadius (other than the target zip in the first row) is in worksheet cell A2.  We use this as the second address in the calculation, so when we copy the resulting formula it will apply to all zip codes in each row.  The formula we obtain from this is:

=CDXRouteMP(1,0,"07869",A2)

We can copy this formula all along the list in Column C to get all the driving distances for all zip codes.  Columns A and B show the zip codes and their straight-line distances from the target, as calculated by CDXRadius, and colums C is the calculated driving distance from CDXRouteMP.   The first few rows are shown here.

Now just use Excel’s sort or auto-filter to find zip codes within a 20-mile driving distance.  When using auto-filter, select a “Number Filter” of “Less than” 20 for column C, and only those rows with distances of 20 miles or less will be visible in the worksheet.

What if you want to find a radius based on driving time?  The process is very similar, but when using the CDXRadius function assume that the vehicle is travelling 90 miles an hour (or 1.5 miles per minute) to obtain the initial list of zip codes.  For a 30-minute driving time, this means the specified (straight-line) radius distance would be 1.5 x 30 or 45 miles.  You can then narrow down the list obtained from CDXRadius by using the CDXRouteMP function to calculate driving time:

As we did before, sort or auto-filter to obtain only those zip codes within a 30-minute driving time.

If you would like a map of the result, use CDXRadius to output a drive time map to the worksheet:

To see short tutorials about the functions CDXRouteMP and CDXDistance, please refer to the following:

Find Zip Codes in a Radius Using Excel

Driving Distance Calculator in Excel

We now have a new database for CDXZipStream that provides an extensive variety of demographic data for 2010 census tracts. CDXZipStream is our Excel-based zip code software that performs address, zip code, and geographic analysis, including route optimization, geocoding, zip code radius calculations, address verification, and demographics import.

Demographics based on census tract offers significant advantages over zip code data. Since census tracts are about half the population of zip code areas, the smaller size provides a more accurate demographic snapshot.  Also, zip code census data is actually based on ZCTA's, or Zip Code Tabulation Areas, which the Census Bureau use to approximate zip codes. Census tracts, which are precisely defined by the Census Bureau, do not include the possible error associated with using ZCTA's.

The CDXTract feed provides a variety of data from the 2010 Census and the on-going American Community Survey.  Topics are:

- Age

- Individual Earnings

- Educational Attainment

- Household Size

- Household Income

- Housing Value and Rent

- Marital Status

- Population

- Race

- School Enrollment

- Land, Water Area

For a complete listing and description of each demographic variable, you can download an Excel file which describes each CDXZipStream data feed in detail, including CDXTract.

To access the data, census tract codes (also referred to as FIPS codes), must be provided as input in an Excel worksheet. CDX Technologies now offers a free Excel template that can identify census tract codes for an address list – please see our blog posting Get Census Tract FIPS Codes in an Excel Template.  You can also view a short Youtube tutorial on how to use the template: Get Census Tract FIPS Codes in Microsoft Excel.

Once you have the list of FIPS codes for each location, use your cursor to select the first code in your list, then click on the CDXZipStream icon on the main toolbar. You’ll see a dialog like this:

We’ve selected the CDXTract data feed (under the Select Data Feed option), and have also added the data fields we need to the list on the right, in this case covering median age data and earnings levels.

Click "Get Data", and the selected data will now be automatically returned to the worksheet (only part of the returned data is shown here):

For a short tutorial on how to use the CDXTract data feed, please see the YouTube video Census Tract Demographics for an Address List.

Between the recent 2010 Census and the ongoing American Community Survey, there is now an unprecedented level of extensive, up-to-date demographics data available for the U.S. market.  Please note that census tract data is an additional purchase, and is compatible with all demographic versions of CDXZipStream.  For pricing information and access to the CDXTract database, please contact us at 1-877-CDX-TEC1 (1-877-239-8321) or support@CDXTech.com.

In our Microsoft Excel add-in, CDXZipStream, there are three data feeds (CDXState, CDXCounty, and CDXCBSA) that provide population estimates for their respective geographies for each of the past ten years.  (These data feeds are available in the CDXZipStream Premium and Premium ACS versions.)   These population estimates are developed by the U.S. Census Bureau, and the entire time series of estimates beginning with the most recent decennial census is revised annually. Ultimately these provide an extremely accurate historical picture of population growth trends over the previous decade, a very useful piece of information for many industries ranging from home construction to car sales.

To access these population estimates within Microsoft Excel, select the first worksheet cell that starts your list of geographic locations.  For instance, here is a list of counties in an Excel workbook:

When working with county lists, use the format "County|State", where a vertical bar delimiter is used to separate the county name and state two-letter abbreviation.  When working with state lists, you must also use the state two-letter abbreviation.  For Core-Based Statistical Areas (CBSA’s) use the five-digit designation by the Office of Management and Budget, which can be found on the census website.   For instance, the Akron, Ohio CBSA has the five-digit designation 10420.

After selecting the first cell in the list, click on the CDXZipStream button on the main toolbar, and on the main dialog select the appropriate feed (in this case, CDXCounty) and desired years of estimated data:

Click “Get Data” and the requested data for the last ten years is returned to the worksheet (only partial data is shown here):

You can now use Excel’s calculational and charting capabilities to look for population trends.  For instance, do you need to locate a franchise to take advantage of high population growth within a particular county, state, or CBSA?  The combination of CDXZipStream and Microsoft Excel can quickly help you quantify trends that will allow you to make decisions based on hard, accurate data. 

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.