Skip to main content Skip to footer

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.

See Spread in Action

Get the Latest Version of Spread Today

Download now!

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

UNIQUE Syntax

UNIQUE(array,[by col],[unique only])
array Array or range containing values or vectors to be compared.
by_col Whether to compare values by column or by row (default is FALSE, by row).
unique_only Whether to return only unique values or vectors (occurring exactly once, default is FALSE).

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:

horizontal array

Figure 1

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

unique function

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:

formula

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:

cell range array

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:

unique function

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:

unique function

Figure 6 Case 2

unique function

Figure 7 Case 3

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

unique]

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:

unique function

Figure 9 Case 5

unique function

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:

unique function

Figure 11 Case 7

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

unique function

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:

unique function

Figure 13 Case 9

UNIQUE in GrapeCity Products

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

See Spread in Action

Get the Latest Version of Spread Today

Download now!

Sean Lawyer

Sean Lawyer

Product Manager
comments powered by Disqus