Applying CDXZipStream Arrays to Large Data Sets

Our Excel add-in CDXZipStream can return zip code radius and list data in the form of an Microsoft Excel array. An array is a group of related values that each occupy its own worksheet cell in an Excel worksheet.  It's often desirable to use arrays in cases where the returned data is too large for single worksheet cell.  We're going to review here how you can apply CDXZipStream arrays to large data sets.  This information applies to the right-click functions CDXRadius and CDXZipList, both of which allow the user to specify arrays as the output.

Let's say we're interested in finding all the zip codes within a 100 mile radius of target zip code 08033.  We can use the right-click function CDXRadius, with the following input:

The returned data is in an array two columns wide, showing both the zip codes within the radius in the first column, along with its distance in miles from 08033 in the second column.  Here's what the first few rows of the array look like:

If you click on any individual cell within the array, the formula is: {=CDXRadius("08033",50)}.  The custom function formula is surrounded by "curly" brackets indicating it is part of an array.  Note that the array acts as a single entity, such that you can't change or delete only part of the array; the array must be deleted in its entirety.

But what if we need to find zip codes within a 50 miles radius for a list of target zip codes?  It's possible to copy and paste the array to easily apply it to more than one target zip – here's how:

1.  Input the first zip code in cell A1 of a worksheet.

 2.  Right-click on cell A2, and select "Insert CDXRadius Function".

 3.  Use these inputs:

     Zip Code: A1

     Radius: 1000 (this will be changed later)

     Distance Unit:  distance in miles

     Result Out:  Array Formula

     Maximum Rows: 10000

4.  When you click OK, an array will be returned for the first zip code.   We purposely made this an extra large array with a 1000 mile radius, so when we copy and paste it for other zip codes we will be sure to have an array large enough to capture all the returned zips.  

5.  Now with the first array highlighted, use Excel's find and replace function to replace all "1000" values with "50" (while leaving out the quotation marks). 

6.  This array can now be copied and pasted immediately below any zip code to find the list of zip codes within 50 miles. 

You may want to experiment a bit with the size of the first array, by changing the radius distance or the number of maximum rows.  Just make sure it is large enough to capture all the data you need for subsequent target zips.

You can copy and paste the array manually, or record an Excel macro that can automate this process for you.  To prevent Excel from slowing down due to the large number of array formulas, just remember to occasionally copy and "paste special" the arrays as values as you proceed.

Even beyond their use in CDXZipStream, arrays can be powerful calculational tools for your worksheeets. For more general information about using Excel arrays, please check out the Microsoft website.

Add comment

Loading