JavaScript and .NET Spreadsheet Tips and Tricks: UNIQUE Function

The UNIQUE function is a very useful but relatively recent addition to Microsoft Excel. UNIQUE is one of the new Dynamic Array Functions, which supports accepting arguments that are dynamic arrays or spilled array references using the new '#' operator (e.g. "A1#") to reference the dynamic array in cell A1).

Learning how to use UNIQUE will greatly enhance your Excel formula calculations for many common use cases and make developing your applications that much easier.

You can find the examples in this blog for UNIQUE in this Excel workbook.

UNIQUE Syntax

The UNIQUE function takes an array or range of values and returns an array of unique items. This function is flexible with its definition of “unique” depending on whether you specify TRUE or FALSE for the optional unique only argument – if you specify TRUE, then the function searches the array for only the unique items that occur exactly once and returns a list of those items only.

However, the default behavior is FALSE, which “cleans” the array of duplicates and return a list of each item in the original array with no duplicates.

These first examples of UNIQUE show how you must specify TRUE for by col when array is a horizontal array that spills to adjacent columns, or else UNIQUE will not return the unique items as expected:

Figure 1

These next examples of UNIQUE show similarly how to work with a vertical array:

Figure 2

The formula in cell E22 shows the most common usage of UNIQUE – specifying a vertical array or reference to a range of cells in one column does not require any other arguments to return the “cleaned” list of unique items.

This is equivalent to the formula in cell H22 which specifies the default value FALSE for by col. The formula in cell K22 shows using TRUE for unique only and returns only one item in this case, 3, since this is the only unique value in the range.

You should not specify TRUE for by col when array is a vertical array that spills to adjacent rows:

Figure 3

The UNIQUE function gets more complicated when used with a two-dimensional array of values or a cell range – the following examples reference this hard-coded array ={1,1,2,1,3;1,1,2,1,3;1,2,3,2,5;1,2,3,2,5;1,2,2,2,5} spilling to adjacent cells and filling the range D29:H33:

Figure 4 UNIQUE Function Cell Range Array Data

When the array is two-dimensional, then it's treated as a list of row vectors when by col is FALSE or not specified, and as a list of column vectors when by col is TRUE.

Comparisons are performed between vector values, and two vector values are considered the same when each vector component (corresponding cell value) is the same.

This is somewhat complicated, so in order to illustrate this clearly, I will split this example into 9 cases referencing the above array in all the possible ways and explain each outcome in detail.

Case 1 simply references the range D29# and leaves out the optional arguments:

Figure 5 Case 1

This returns three unique rows since the default values for by col and unique only are both FALSE. The next two cases, 2 and 3, are related and show the same result by specifying the default value(s) for those arguments:

Figure 6 Case 2

Figure 7 Case 3

Case 4 shows the result of specifying FALSE for by col and TRUE for unique only, returning one unique row:

Figure 8 Case 4

Cases 5 and 6 show the result of explicitly specifying TRUE for by col and FALSE for unique only, using the default value in Case 5 and explicitly specifying FALSE in Case 6, thus returning four unique columns:

Figure 9 Case 5

Figure 10 Case 6

Case 7 shows the same result as cases 1, 2, and 3, leaving by col using the default value FALSE and explicitly specifying FALSE for unique only:

Figure 11 Case 7

Case 8 shows the result of specifying TRUE for by col and unique only, which results in three unique columns:

Figure 12 Case 8

Finally, Case 9 shows the same result as case 4, leaving by col using the default value FALSE and explicitly specifying TRUE for unique only:

Figure 13 Case 9

UNIQUE in GrapeCity Products

The UNIQUE function is supported in these GrapeCity spreadsheet controls and components:

• Spread.NET Windows Forms Spreadsheet Control | Explore demos)
• SpreadJS Javascript/Typescript/Angular/React/Vue Spreadsheet Widget | (Explore demos)
• GrapeCity Documents for Excel .NET and Java Controls | Explore the .NET demo | Explore the Java demo)