highlight duplicate rows

[James]

I need a way to highlight rows that are dupilcates based on what I have in
column A. I have tried doing this through conditional format but I have
crashed and burned on that one. So here is simplier example of what I have:

Column A
22
22
22
16
16
8
73
73

Basically I would like to have all the 22 entries highlighted and when I
move down I don't want the 16's highlighted but when I move to the 8, I wan't
that highlighted, and not to highlight the 73's. It would need to highlight
every other group of duplicates (or single entry).

I need a way to automate this because I have something like 23,000 rows of
data and do not want to go through the entire thing one by one. Thanks for
your help.

 

[Sean Timmons]

I can see using a helper column.

Let's say the helper column is B and data starts on row 2.

Cell B2 would be 1

Cell B3 would be =IF(A3=A2,B2,B2+1)

Paste to bottom

go to Conditional formatting.

Formula is

=MOD($B2,2)=1

Pick your highlight color

Paste to bottom

 

[FiluDlidu]

Conditional format from row 2 down, using the following formula:

=MOD(SUMPRODUCT(--(A$2:A2<>A$1:A1)),2)=1

Warning, though: unlike Sean's option to use a helper column, it MIGHT slow
down your sheet a bit if you do it for 23,000 rows.

 

[James]

Wow this is awesome. It works great, but is there a way to make it highlight
the entire and not just that cell? Other than that it did exactly what I was
looking for.

 

[James]

I figured out how to apply it to the rest of my data set, thank you very much
for the formula.