List Unique Items in a Range

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.
Attachment | Size |
---|---|
ListUniqueItemsInARange.xlsx | 8.95 KB |
- Almir's blog
- Login or register to post comments
- 4600 reads
Recent comments
5 years 50 weeks ago
6 years 36 weeks ago
6 years 47 weeks ago
6 years 50 weeks ago
6 years 51 weeks ago
7 years 5 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago