Search code examples
excelexcel-formularowunique

Excel formula not working - returning unique values horizontally

Is there a way to display unique values from my dataset horizontally WITHOUT using the transpose function:

I have a table like this from G6-G15:

**Colour**
Red
Red
Blue
Green
Green
Green
Yellow
Yellow
Yellow

My required output is this

Blue Green Red Yellow

I have tried using this formula to return the unique values horizontally however, it doesn't seem to work =INDEX(SORT(UNIQUE($G$7:$G$15)),COLUMN(G6))

Solution

  • We can use SEQUENCE to SPILL right:

    =INDEX(SORT(UNIQUE($G$7:$G$15)),SEQUENCE(,COUNTA(UNIQUE($G$7:$G$15))))

    enter image description here

    Or we can do the old fashioned drag with:

    =INDEX(SORT(UNIQUE($G$7:$G$15)),COLUMN(A1))

    enter image description here