highlight duplicate rows
 

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.

highlight duplicate rows
 

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

"James" wrote:

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.

highlight duplicate rows
 

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" wrote:

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.

highlight duplicate rows
 

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.


"FiluDlidu" wrote:

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" wrote:

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.

highlight duplicate rows
 

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

"FiluDlidu" wrote:

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" wrote:

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.