Syntax of the UNIQUE Function

August 18, 2022 - by Bill Jelen

The new UNIQUE function is part of the Dynamic Array formula collection. It returns the distinct values from an array.

The syntax of this function is =UNIQUE(array, [by_col], [occurs_once]). In this syntax, array is any array. For by_col, the choices are True to compare by column and False to compare by row. It seems that comparing by row is the logical choice, and it is the default if you omit the second argument. The optional occurs_once argument is interesting and brings me to a small rant.

Consider this list: Apple, Apple, Banana, Cherry, Cherry. What would you say are the unique items in the list? If you are not a database pro, you would say the correct answer is Apple, Banana, Cherry. But several features in Excel would say that Banana is the only item in the list that has no duplicates. For example, if you select Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values, Unique, Excel will highlight only Banana. Database pros say that Apple, Banana Cherry is a list of distinct values and that Banana is the only unique item.

Who would ever care about a product that was sold once? Unless you are listing one-hit wonders, this definition of unique seems useless. We'll send this one out as a long-distance dedication to Casey Kasem in the afterlife.

A list of music artists appear in column B. The Beatles and Elvis are listed repeatedly. To get the list of One-Hit Wonders, use =UNIQUE(B2:B12,False,True). The True in the third argument means Occurs Exactly Once. The results of this formula are Los Del Rio, Bobby McFerrin, Tony Basil, Vanilla Ice and other artists with exactly one hit. — Figure 623. Return a list of items that occur exactly once.

The great news is that the UNIQUE function can be used a couple ways. In this case, you could have it return Apple, Banana, Cherry (the default), or you could change the third argument and get just Banana.

The syntax of UNIQUE is Array, By Column, Occurs Once. Only Array is required. Two uses of UNIQUE are shown. From the list of Apple, Apple, Banana, Cherry, Cherry, the unique function can either return Apple, Banana, Cherry or it can return Banana as Banana is the only item occurring exactly once. — Figure 624. Which would you say is the true unique list?

The rest of the examples in this topic use the following data set.

The data has four columns: Team, Name, Product, Score. Headings are in row 3 and the data runs from row 4 to 29. — Figure 625. Team, name, product, and score data.

Using UNIQUE on this data is simple. Because the second and third arguments are optional, you can use =UNIQUE(C4:C29) to return a list of distinct products.

=UNIQUE(C4:C29) returns a list of unique products: Apple, Orange, Lemon, Lime, Quince, Kiwi. Notice the list is not sorted - it corresponds to the original sequence found in the source data. — Figure 626. Returning a list of unique products.

The chapter of Mike Girvin's Ctrl+Shift+Enter book on getting unique values just became one sentence. I love his book (and I am the publisher of that book), but it is about to become a pamphlet with all of these simplifications!

What if you need every unique combination of name and product? You can use a two-column array: =UNIQUE(B4:C29). To get every unique combination of Team and Product, use: =UNIQUE(CHOOSE({1,2},A2:A39,C2:C39)). See https://youtu.be/fdD8c1xBU0g for details.

A formula that points to two columns will return every unique combination of Name and Product: =UNIQUE(B4:C29). — Figure 627. Every unique combination of two values.

This article is an excerpt from Power Excel With MrExcel

Title photo by Omer Nezih Gerek on Unsplash