MrExcel.com - Your One Stop for Excel Tips & Solutions

Find One-Hit Wonders with UNIQUE

October 04, 2018 - by

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