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
- 4948 reads


Recent comments
6 years 36 weeks ago
7 years 22 weeks ago
7 years 34 weeks ago
7 years 37 weeks ago
7 years 38 weeks ago
7 years 43 weeks ago
7 years 51 weeks ago
8 years 1 day ago
8 years 1 day ago
8 years 2 days ago