CODEWITHSUNDEEP
excelexcel-formulauniquehorizontallist
Girl007
Girl007

Reputation: 175

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

Upvotes: 4

Views: 17544

Answers (2)

JvdV
JvdV

Reputation: 75870

With Microsoft365:

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

enter image description here

Upvotes: 9

PatrikN
PatrikN

Reputation: 129

Thanks for the tips! I needed to do the opposite, with values in horizontal row to unique values in a column, which I solved with this:

= UNIQUE(TRANSPOSE(K3:AL3))

With the unique values starting at AO4, I could then make sums for each unique value with the source values in row 3 and numbers to sum in row 4.

= SUMIFS($L$4:$AL$4; $L$3:$AL$3; AO4)

This way I could get a list like a pivot table, which requires "headers, with a single row of unique, non-blank labels for each column", and since I had multiple equal values in the "header row", I don't think I could use an actual pivot table, but this worked fine too.

Upvotes: 1

Related Questions