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:
Guidlines and Examples of Array Functions
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