List Unique Items in a Range

Almir's picture

If you need a list of distinct/unique items from the range (column A) where items appear more than once, select a range of the same size (column C) and enter this as an array formula (press CTRL+SHIFT+ENTER):

=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:" & ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:" & ROWS(Data))))),"")

Where "Data" is a named range containing original list (column A).

Named range is not mandatory, but I recommend you create it, so formula is easier to handle.

Note that formula will not work if any cell in "Data" range is blank.

Check example file attached.

List unique items in a range
ListUniqueItemsInARange.xlsx8.95 KB