Search code examples
google-sheetsgoogle-sheets-formula

How can I use conditional formatting to check if values repeat in a column non-consecutively


A little similar to this question: How can I use conditional formatting to check if a number repeats consecutively in a column?

But what I'm looking to do is to find if a value repeats more than once in a non-adjacent cell. So for example this is fine:
A
A
B
B
C
But this needs to be highlighted:
A
A
B
C
B <-- out of order

(they won't always be sorted like that, so alphabetically sorting options is not an option)

Couldn't find anything similar to this in other posts.


Solution

  • You may find useful in A2:A (don't start in the first row but in the second) a formula like this in conditional formatting:

    =AND(A2<>"",A2<>A1,COUNTIF($A$1:A1,A2))
    

    enter image description here