Highlight 2nd Instance duplicates in sheets

1
votes

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.

2
google-sheets
stackoverflow.com/help/someone-answers - player0

2 Answers

3
votes

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.

-2
votes

you need to lock it with $

=COUNTIF(A$1:A, A1)>1

enter image description here

update:

=COUNTIF(A$1:A, A1)=2