Our address and zip code analyzer, CDXZipStream, includes a function called CDXRouteMP that performs a wide variety of tasks involving driving calculations, including route optimization.  We’d like to summarize here the variety of output information CDXRouteMP currently provides since it’s grown to be quite a substantial list that not all of our users may be familiar with.

First, let’s take a quick look at all the route calculation choices available through the input box.  This can be accessed by right-clicking on any cell in your Excel worksheet, then selecting CDXRouteMP from the CDXZipStream functions list:

Route calculations based on the shortest and quickest driving route options will result in the shortest driving distance and quickest driving time, respectively.  Calculations based on the preferred route will take into account preferred road types (such as highways, toll roads, arterial roads), which you can specify under the Route Settings Road Types tab.  Also see our post Using Microsoft MapPoint Settings with CDXZipStream for more information.

One more item worth noting:  If you are optimizing a route, the order of the intermediate stops is always optimized based on achieving the quickest driving time.  However, all the other output, such as driving time, distance, trip duration, and cost will be based on the selected route option:  shortest, quickest, or preferred.   Driving directions from waypoint to waypoint will also be based on the selected route option, although the order of the waypoints will again be based on the quickest driving route.

Here are some great tips that can be applied to all versions of CDXZipStream:

1.  Stop using CDXZipStream  

Just kidding! But what we mean is you don’t need to click on the CDXZipStream button, or access the right-click menu, to get data from CDXZipStream.  If you’ve already applied CDXZipStream in your worksheet and need to use it in another data area, just copy and paste the existing equations to the new area and the formulas will get the data automatically.  This also works in cases like route optimization where the data is output as an array; just make sure to copy the entire array when applying it to a new area.  Here’s a step-by-step description of how to copy a CDXZipStream array to a large data set.  

2. Don’t scroll through your data 

When you’re dealing with a large set of addresses (and getting lots of data back from CDXZipStream), it can be slow and frustrating using the Excel scrollbar to navigate through it all.  Fortunately there are some very useful shortcuts that can quickly get you exactly where you need to go in your worksheet.  They’re easy to learn with a little practice, and even if you’re an occasional Excel user, we guarantee it’s worth the effort:

Ctrl – Arrow:  To quickly skip to the boundaries of your data, hold down the Ctrl key and one of the directional arrows on your keyboard at the same time. (These arrows are usually located on the right side of your keyboard, and may also be on the number pad on the far right.)  For instance, to go to the bottom of your data set, use the cursor to select a cell somewhere within the data, then hold down the Ctrl and Down-Arrow keys at the same time.  The cursor will move to the very last row of contiguous data.   You can easily move to the top, right, left, and right boundaries of your data using the appropriate arrow keys, and you can continue to press the arrow key to navigate to other data sets.  After skipping through all the data you’ll eventually reach the very last row or column of the worksheet.

Ctrl – Shift - Arrow:  To select a large area of your data, hold down the Ctrl and Shift keys, and one of the directional arrows on your keyboard at the same time.   For example, to select the first column of your data, select the top left data cell with your cursor, then hold down the Ctrl – Shift – Down arrows together.   Then to select the entire data set, hold down Ctrl – Shift – Right arrows together.  With a little practice you’ll find that this by far the easiest way to select a contiguous area of data, regardless of its size.

F5:  Sometimes you just need to go to one particular cell.  Hit the F5 function key at the top of your keyboard, and you’ll see an input box where you can enter the cell address.  Then just press the enter key, and you'll instantly arrive at the desired cell.  

3. Speed up Excel  

If you have thousands of equations (or more) in your worksheet, you may notice that Excel starts to slow down.  That’s because every time a cell in the worksheet is changed, Excel by default recalculates all equations.  This will also occur with the custom function formulas CDXZipStream uses to obtain data.  To speed up your worksheet, you have a couple of options:

The first is to turn off automatic recalculation.  In Excel 2010, from the File tab, select “Options”, then “Formulas”, then under “Calculation options” select “Manual”. (A slightly different procedure is required for Excel 2003 and 2007.) You can now control when Excel recalculates by pressing F9 on your keyboard or by selecting “Calculate Now” on the Formula tab.  Since recalculation only impacts existing formulas, you can continue to get new data from CDXZipStream.  Just remember to turn recalculation back on when you’re done with your workbook.

Another easy approach is to replace the equations with their values.  Select the range of equations in your worksheet, and in Excel 2010 from the Home tab, select “Copy” from the Clipboard group. Then click on the arrow under “Paste” and select the first “Paste Values” icon on the left. (This can also be done in Excel 2003 and 2007.)  The equations will now be gone, but the values will remain. And you’ll notice that Excel is much faster and more responsive. 

Note:  If you'd like to create a toolbar shortcut for pasting values, which is one of the most common tasks in Excel, please follow these instructions. 

4. Use cell references with right-click functions

 When accessing a right-click function like CDXDistance or CDXRouteMP, you have the option of inputting an actual value (like the zip code “38472”) or the cell reference where the data is located (such as “A1”).  If the value is located somewhere in the worksheet, you should almost always use the cell reference.  Not only is it easier in most cases, but the resulting CDXZipStream equation in the worksheet will now be ready to copy and paste to other data areas without need for modification.  Since “A1” is considered a relative cell reference, Excel will automatically change this reference when moved to the new data area.  In cases where an absolute (unchanging) cell reference is required (when calculating distances to a single location), you can either input the actual value or use absolute reference notation with the dollar symbol, like this:  “$A$1”.  

5. Rev up CDXZipStream using Auto Fill

We’ve talked about auto fill before in a previous blog, but it bears repeating because it can really speed up CDXZipStream when you’re working with a lot of data (> 10,000 calculations).  

Let’s say you need data for a very long list of zip codes.  First, use the CDXZipStream button to get custom formulas for only the first zip code in your list – to do this you will need to insert a blank row after the first zip code.  Then delete the blank row and use the Excel auto fill feature for the remaining items in the list.  Please refer to this Microsoft article about auto fill – just remember you can automatically fill a formula downward, for all adjacent cells, by double-clicking the fill handle of the first cell – dragging the handle is difficult for long data lists.  Not only is auto fill faster, but Excel will also show you the progress of the calculations.  When auto fill is finished, you can now use tip number 3 to manage all those new formulas.

You may have noticed that a few of these tips work well with any large set of data, whether you're using CDXZipStream or not.  We hope these can significantly help improve your experience while using both CDXZipStream and Microsoft Excel.

In a blog post two weeks ago we talked about how you can customize some of our CDXZipStream Excel templates to meet your specific needs.  However, when it comes to more complex calculations, especially for very large data sets, do-it-yourself customization may not be enough.  When you need an Excel template for zip code, address, and other location-based analysis, and our CDXZipStream free templates don’t fit the bill, consider contacting us for a free quote on a custom template built just for you.  In many cases we can build on our past experience, allowing for very quick (less than 5 business days) turn-around time on your project.   Here are a few examples of the kinds of analyses a custom template can perform:

Multiple Route Optimization:  When managing sales call schedules or customer service visits, it may be necessary to perform repeated route optimizations on a frequent basis.   In some cases, the number of stops on a route can be fairly large, up to 50 or more addresses that need to be included in the optimization process.  Although we do offer a free template that performs optimization for a single route, running this multiple time may not an efficient use of your time.   A customized template can be specifically designed to manage multiple routes, and also be customized to make input of multiple address groups quick and easy.  

Radius Analysis or Route Optimization with additional data:  Address records usually include a lot of other associated data, such as name, telephone number, and other identifying numbers or historical information important for your organization.  When radius analysis or route optimization re-orders a long list of addresses, you can retain all associated data using a customized CDXZipStream template.  Field column headings can be custom-designed for your particular set of data, or left blank so you fill them in yourself.   

Multiple Radius Analyses with sorting:  Need to assign all customers to all your sales staff based on geographic proximity?  When doing multiple radius analyses involving a large x by y matrix of locations, it may be important to sort the results for each analysis based on some other variable.  For example, it may be necessary to find all customers within a 50 mile radius of each salesperson, then sort the customers for each based on account size, distance, product type, or any other data that may be helpful in deciding sales staff assignment.  Even without sorting this process can involve literally millions of calculations.  A customized template can handle the multiple radius analyses as well as automate the multiple sorting involved. 

Driving Distance Radius Analysis for very large data sets:   Some radius analyses require driving distance calculations for added accuracy.  Driving distance calculations are inherently slower than straight-line calculations, so when performing radius analyses for a large x by y matrix of locations, it’s best to take a two-pronged approach.  First, perform straight-line calculations for a wider radius than desired to narrow down the choices; for instance, if you need to find locations within a 25 mile driving radius, calculate the straight-line distance to all possible locations within a 50 mile radius.  Then from these results, perform driving calculations to determine which fall within the 25 mile radius.  Again, this technique involves many iterative calculations, including the use of mapping software like Microsoft MapPoint, that can best be handled in an automated, customized template.  

Think a customized template could work for you?  Contact us for a free quote at customsolutions@cdxtech.com or call 1-877-CDX-TEC1 (1-877-239-8321).