Cat On A Spreadsheet

One of the less obvious strengths of dynamic arrays is that they make it much easier to compare datasets that do not necessarily have the same shape. In everyday reporting, this is an extremely common requirement. We might have this month's transactions and last month's transactions, a current customer list and a previous customer list, two extracts from different systems, or a list of expected records and a list of records that were actually received. The traditional approach is often to place the two datasets beside each other and build a collection of helper formulas to identify what has changed. Dynamic arrays allow us to approach the problem more directly. Rather than comparing cells, we can compare arrays.

The simplest example is asking whether the contents of one list exist in another. Suppose we have two lists of product codes. The first represents products that were sold this month, while the second represents products that were sold last month. If we want to identify products that appear in this month's data but did not appear last month, XMATCH provides a particularly useful starting point.

The formula first creates distinct lists for the two periods. XMATCH then attempts to find every current product in the previous list. Where no match exists, XMATCH returns #N/A. ISNA converts those errors into TRUE or FALSE, and FILTER uses that result to return only the products that are genuinely new to the current period.

This is a very different way of constructing a report from the familiar XLOOKUP approach. With XLOOKUP, we normally have a record and want to retrieve information about it. With XMATCH, we can simply ask whether the corresponding key exists.

That makes XMATCH particularly useful for set comparison. If we reverse the two arrays, we can identify products that were present last month but have disappeared this month:

The symmetry is useful. We are not writing an entirely different solution for the second question. We are simply reversing the direction of the comparison. This distinction between new, removed and unchanged records is at the heart of many reconciliation reports.

We can also identify records that exist in both datasets:

Now ISNUMBER is used because a successful XMATCH returns a position, which is numeric. The result is effectively an intersection between the two sets. Once we start thinking in those terms, a surprisingly large number of reporting problems become easier to describe. We have two populations, and we want to know which records belong exclusively to one population, exclusively to the other, or to both.

The next complication is that a comparison often needs to consider more than the existence of a record. Suppose a product appears in both months. It is not necessarily unchanged. Its quantity may have increased, decreased or remained the same. Now we need to compare attributes belonging to the matching records.

Imagine that each monthly table contains ProductCode and Quantity. We can begin by establishing the complete set of products appearing in either period.

One straightforward approach is to combine the two product-code arrays and then use UNIQUE:

VSTACK is doing something important here. It does not compare the arrays. It simply places one underneath the other. UNIQUE then removes the duplicates. This gives us a common dimension against which we can compare the two datasets.

We can now retrieve the quantity for each product from both periods:

This is already a useful comparison report. Every product has a row, regardless of whether it appeared in one period, the other period, or both. A product that existed only in the current period will have a zero for the previous period. A product that existed only in the previous period will have a zero for the current period.

We can then calculate the difference:

We have now transformed two independent transaction datasets into one comparative dataset. That transformation is worth examining carefully because it illustrates a broader principle. We did not try to align the original tables row by row. That would be unreliable because there is no guarantee that the same product occupies the same row in each dataset. Instead, we created a common set of keys and then independently aggregated each dataset against those keys. The key establishes the relationship between the datasets. The original row positions become irrelevant.

This is precisely the kind of problem that database systems solve with keys and joins, and dynamic-array formulas allow Excel to perform surprisingly similar operations directly on the worksheet.

We can take the comparison further by calculating percentage change.

The IF is important here because percentage change is undefined when the original value is zero. A product that sold zero units last month and ten this month has clearly increased, but describing that increase as a percentage is mathematically problematic.

This is a good example of something that can easily be hidden by an apparently simple formula. The arithmetic is trivial; the reporting decision is not. Should the report display 1000%? Should it display "New"? Should it show N/A? Should the row be excluded from a percentage-based ranking altogether? Those are business decisions, and dynamic arrays do not make them disappear. They simply give us the tools to implement whichever interpretation makes sense.

For example, if we wanted to label products with no previous-period quantity as "New", we could use:

The resulting array now contains both numeric and textual values in its final column. That may be perfectly appropriate for presentation, although it means that column is no longer purely numeric and therefore needs to be treated accordingly if we subsequently want to perform mathematical operations on it.

This brings us to another useful aspect of dynamic arrays: once we have built a comparison array, we can filter the comparison itself. Suppose we want only products whose quantity increased:

Or only products whose quantity has fallen:

Notice how little duplication there is. Once the comparison has been expressed as arrays, filtering the result becomes no different from filtering any other dataset. This composability is one of the most important characteristics of dynamic-array formulas. A function does not necessarily need to know anything about where its input came from. FILTER does not care whether the array it receives came directly from a table, from XLOOKUP, from HSTACK, from VSTACK, or from an elaborate calculation involving several other functions. If the result is an array, it can become the input to another array operation. That allows us to build formulas in stages.

There is another particularly useful application of this principle when comparing datasets from different sources. Imagine that one system produces a list of customer IDs while another produces a list of customer IDs and status information. We want to identify customers whose status has changed. The first step is again to establish a common set of keys. We can then retrieve the status from each source.

The final FILTER returns only customers whose status differs between the two datasets.

There is an interesting extension to this idea when the datasets contain duplicate records. Suppose a customer appears several times in the transaction table. We do not want to compare individual transaction rows. We want to compare the customer's total activity.

This is where the aggregation techniques from the previous post become essential. We first create the common set of customer IDs, then aggregate each source against that set. The comparison therefore becomes independent of both row order and row count.

That is one of the strongest arguments for treating a report as a sequence of transformations rather than as a collection of formulas attached to cells. Once the data has been reduced to the correct analytical grain, the comparison becomes straightforward.

The phrase analytical grain is useful here. A transaction-level dataset has one row per transaction. A product-level dataset has one row per product. A customer-level dataset has one row per customer. If we compare two datasets without first considering their grain, we can easily end up comparing things that should not be compared directly. Dynamic arrays make it easy to manipulate the data, but they do not determine the correct grain for us. That remains part of the design of the report.

There is also a useful role for TAKE and DROP once a comparison has been sorted. Suppose we have calculated the change for every product and sorted the result by the size of the increase:

The final TAKE reduces the result to the ten largest increases. The same pattern could be used for the ten largest decreases by sorting in the opposite direction, or for a report showing only the first few rows of a much larger analytical result.

This is a good example of how the individual functions in this series begin to form a vocabulary.

FILTER gives us the records we want.

UNIQUE gives us the distinct groups.

XLOOKUP and XMATCH establish relationships between datasets.

SUMIF and related functions aggregate values against those groups.

VSTACK and HSTACK construct new datasets.

SORTBY controls their order.

TAKE and DROP control how much of the resulting array we expose.

LET gives names to the intermediate stages so that the whole calculation remains comprehensible.

None of these functions is particularly revolutionary on its own. The real change comes from being able to combine them without having to create a worksheet full of intermediate ranges. That is what turns dynamic arrays from a collection of useful functions into a genuine reporting technique.

There is, however, another problem we have not yet addressed. So far, our examples have generally started with data that already exists in a reasonably usable form. We have selected it, enriched it, grouped it and compared it. Real-world reporting data is rarely so cooperative. Sometimes the source contains several columns that need to be combined. Sometimes different systems use different structures. Sometimes we need to remove irrelevant fields, rename the conceptual role of columns, or construct a completely new reporting dataset from several sources. In other words, we have learned how to analyse arrays. The next step is to become more deliberate about building the array that we actually want to analyse.

That takes us to the next post, where we will bring the techniques together and look at how a complete report can be constructed from a raw dataset rather than merely extracting individual results from it.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet