excelexcel-formulauniquehorizontallist

How can I return unique values horizontally on Excel


I have a table that looks like this from A1-A10

**Animal**
Zebra
Zebra
Zebra
Giraffe
Giraffe
Monkey
Monkey
Cat
Cat

I want to return ONLY the unique values HORIZONTALLY in a row - and have them sorted alphabetically

like this:

Cat   Giraffe   Monkey   Zebra

Currently I have this formula in D3: but this returns unique values vertically

=IFERROR(LOOKUP(2, 1/(COUNTIF($D$2:D2, $A$2:$A$10)=0), $A$2:$A$10), "")

Zebra
Monkey
Giraffe
Cat

Solution

  • With Microsoft365:

    =TOROW(SORT(UNIQUE(A2:A10)))
    

    enter image description here