'Highlight 2nd Instance duplicates in sheets
Trying to mark duplicates on google sheets, but I see that First Instance is highlighted when I use
=COUNTIF(A1:A100, A1)>1.
I tried to use IF and COUNTIF together so that the 2nd Instance gets highlighted.
=IF(COUNTIF(A1:A97,A1)>1, "Duplicate", " ")
but did not work.
I am trying to use this so that the cell gets highlighted as soon as I enter the duplicate number in the column.
Solution 1:[1]
To Highlight 2nd Instance duplicates in sheets with COUNTIF select ColumnA and: Format > Conditional formatting..., Format cells if... Custom formula is and apply:
=and(countif(A$1:A,A1)>1,countif(A$1:A1,A1)>1)
with highlighting of choice and Done.
Should format not only the second instance but all other than the first.
Solution 2:[2]
you need to lock it with $
=COUNTIF(A$1:A, A1)>1
update:
=COUNTIF(A$1:A, A1)=2
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | pnuts |
| Solution 2 |