I'm trying to show duplicates for a column in google sheets. I can show all duplicates using =countif(B:B,B:B)>1. In this case though, I only want it to highlight them if they are NOT consecutive. For example, if I have: A B B B C D E F B
I would like for only the final 'B' to be highlighted.
The closest code I could find was this: =MATCH(2,1/(A$2:A$13=A2))-MATCH(A2,A$2:A$13,0)<>COUNTIF(A$2:A$13,A2)-1 and it didn't work - it highlights the very first B of the three consecutive ones, and does not highlight non-consecutive ones at all. It has to be only the bottom, non-consecutive one because when someone adds something to the column, they're adding it to the bottom, and they need to know they're adding something that's already there.
I only know python and SQL, I haven't messed with google sheets much before. In Python-ish terms, the logic I'm trying to use is:
B(i or j) = arbitrary cell in column B IF B(i) EXACTLY EQUALS B(j), AND B(i) DOES NOT EQUAL (B(i-1) OR B(i+1)), color = red
I just don't know how to translate that logic to a google sheets conditional format.
Any ideas?? TIA for any help!
Assuming the range starts in A1, you can use the following conditional formatting custom formula:
=AND(A1<>"",COUNTIF(A:A,A1)>1,AND(IFERROR(OFFSET(A1,-1,0)<>A1,0),OFFSET(A1,1,0)<>A1))