If you have Office 365 (recently renamed to Microsoft 365) and Microsoft Excel, you now have access to several dynamic array functions that will enable you to perform many helpful tasks. In this article, we will give you several examples that illustrate the power of the UNIQUE dynamic array function.
The UNIQUE function returns a list of unique entries from a range of data. If you make your data range a table (by selecting the range and using the keystroke combination Control+T), then this function automatically updates the results to include new data entered in the table. The workbook Unique.xlsx (see Figure 1) contains many examples of the UNIQUE function.
Figure 1: Examples of the UNIQUE function
In the cell range D5:E66, we have listed the salesperson and the product sold on 62 sales transactions. We want to easily generate:
- A list of all salespeople.
- A list of all products sold.
- Each distinct combination of salesperson and product.
To accomplish these tasks, we proceed as follows:
- To return a list of all products, simply enter in G13 the formula =UNIQUE(E5:E66). A list of unique products will then appear, beginning in cell G13. Make sure to leave enough space underneath the formula for the results to populate, or you will obtain a #SPILL error.
- To return a list of unique salespeople, enter in cell H4 the formula =UNIQUE(D5:D66).
- To return a list of all unique salesperson-product combinations, enter the formula =UNIQUE(D5:E66,FALSE,FALSE) in cell A12. Figure 2 shows the syntax of this formula. The By_col FALSE (or omitted) ensures that we look for data going down rows, not across columns. Changing the By_col argument to True ensures that we look for data going across columns. The Exactly_once FALSE (or omitted) ensures that all distinct rows are returned. Changing the Exactly_once argument to TRUE ensures that only rows that appear once are returned. Note that our list of distinct rows ends in row 44. As shown in cell J4 the formula =UNIQUE(D5:E66) would yield the same results as =UNIQUE(D5:E66,False,False)
Figure 2: Syntax of the UNIQUE function
Using the UNIQUE function across columns
Figure 3: The UNIQUE function applied across rows, finding unique entries based on multiple columns
The following formulas (see Figure 3) show how the UNIQUE function can be applied across columns:
- In cell R6 the formula =UNIQUE(M3:Q3,TRUE) returns in the range R6:U6 all distinct names found in the range M3:Q3.
- In cell R8 the formula =UNIQUE(M3:Q3,TRUE,TRUE) returns only those names that occur once.
The UNIQUE function and Excel tables
In the worksheet “Table,” we made our data in D4:E66 into an Excel table using the keystroke combination Control+T. Entering in cell G6 (see Figure 4), the formula =UNIQUE(D5:E66) will enable your unique entries to update automatically as new data is entered. Enter a new distinct row of data and watch your results update automatically!
Figure 4: Source data a table so that adding new rows causes UNIQUE results to automatically update
Next month we will discuss the SORT and SORTBY dynamic array functions. These functions provide powerful tools for sorting your data!
