Search code examples
arraysgoogle-sheetsconditional-statementsconditional-formattingcountif

Highlighting first time a value appears in Google Sheets


Is it possible to highlight cells where a value appears for the first time?

Eg:

A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 1
A6 = 2

So in the above A1-A4 would be highlighted but A5-A6 wouldn't as they are repeats of the same value


Solution

  • Now if I wanted the lower numbers to be classed as the first entry, could I reverse this in some way? Ie using the above example it would be cells A3-A6 that would show as the first entries and A1-A2 wouldn't be highlighted

    =INDEX(COUNTIFS(A:A,A1,ROW(A:A), ">="&ROW(A1)))=1
    

    enter image description here

    and the reverse of it would be:

    =INDEX(COUNTIFS(A:A,A1,ROW(A:A), "<="&ROW(A1)))=1