Highlighting Non-Consecutive Duplicates

[Innoguide]

Does anybody have any suggestions on how to create a conditional rule that finds non-consecutive duplicates in a column of data (numbers or text)? For example, the data below have been highlighted (red text) showing the desired result. Note that all occurrences need to be formatted.

AB
AB
AB
ghy
ghy
ABCD
ABC
r
r
ghy
eee
eee

Any help would be greatly appreciated!

 

[Scott T]

In conditional formatting use

Adjust range to match your data, this is for data in A1 though A12.

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

 

[Marcelo Branco]

If i'm understanding what you need, assuming a heading in row 1 and data in A2:A13 , maybe..

Select A2:A13

Use this formula in CF
=COUNTIF(A$2:A$13,A2)<>MAX(FREQUENCY(IF(A$2:A$13=A2,ROW(A$2:A$13)),IF(A$2:A$13<>A2,ROW(A$2:A$13))))
pick the format you want (Font --> red)

M

 

[VBA Geek]

add this a CF rule:

Excel 2010
	A	B
1	AB	FALSE
2	AB	FALSE
3	AB	FALSE
4	ghy	TRUE
5	ghy	TRUE
6	ABCD	FALSE
7	ABC	FALSE
8	r	FALSE
9	r	FALSE
10	ghy	TRUE
11	eee	FALSE
12	eee	FALSE
Sheet2

Excel 2010
	B
1	FALSE
Sheet2

Cell Formulas
Range	Formula
B1	{=COUNTIF($A$1:$A$12,A1)<>(MAX(IF(A1=$A$1:$A$12,ROW($A$1:$A$12)-ROW($A$1)+1))-MATCH(A1,$A$1:$A$12,0)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[VBA Geek]

If i'm understanding what you need, assuming a heading in row 1 and data in A2:A13 , maybe..

Select A2:A13

Use this formula in CF
=COUNTIF(A$2:A$13,A2)<>MAX(FREQUENCY(IF(A$2:A$13=A2,ROW(A$2:A$13)),IF(A$2:A$13<>A2,ROW(A$2:A$13))))
pick the format you want (Font --> red)

M

ok I guess we posted more or less the same thing, I had not seen your reply I promise

 

[Marcelo Branco]

ok I guess we posted more or less the same thing, I had not seen your reply I promise

I believe on you. Happens many times to me

M.

 

[Innoguide]

Oops - meant to quote

 

[Innoguide]

If i'm understanding what you need, assuming a heading in row 1 and data in A2:A13 , maybe..

Select A2:A13

Use this formula in CF
=COUNTIF(A$2:A$13,A2)<>MAX(FREQUENCY(IF(A$2:A$13=A2,ROW(A$2:A$13)),IF(A$2:A$13<>A2,ROW(A$2:A$13))))
pick the format you want (Font --> red)

M

Does the trick -
Thanks Marcelo!

 

[Innoguide]

In conditional formatting use

Adjust range to match your data, this is for data in A1 though A12.

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

Sorry - shows all dups

 

[barry houdini]

Here's another approach. Try this formula in conditional formatting

=MATCH(2,1/(A$2:A$13=A2))-MATCH(A2,A$2:A$13,0)<>COUNTIF(A$2:A$13,A2)-1

If there are any blanks in the range it won't format those