MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find One-Hit Wonders with UNIQUE


October 04, 2018 - by Bill Jelen

Find One-Hit Wonders with UNIQUE

UNIQUE is one of the new dynamic array functions in Excel. Using the second or third arguments, you can control if the function returns the UNIQUE or DISTINCT list of items. You can also control if UNIQUE works row-wise or column-wise.

In the following image, =UNIQUE(A2:A12) returns each item from the list

Database pros call this the distinct list.
Database pros call this the distinct list.

Add a third argument of TRUE to get items that appear exactly once in the list. In essence, the Billboard One-Hit Wonders:


Use True as the third argument for items that appear only once.
Use True as the third argument for items that appear only once.

The Excel version of UNIQUE offers one trick that G**gle Sheets does not offer: you can use UNIQUE to return the unique products across a row. By adding a ByColumn argument of True, Excel will return the unique values going across a row.

Unique values across a row.
Unique values across a row.

Watch Video

Download Excel File

To download the excel file: find-one-hit-wonders-with-unique.xlsm

From now until the end of 2018, I am making my new Excel Dynamic Arrays Straight To The Point e-book free.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"VLOOKUP works! Period."

Title Photo: Shu xin on Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.