Highlight duplicates only if consecutive cells

[GenericEric]

Hi everyone,
Is there a way of only highlighting duplicate cells if they are consecutive (ie touching each other) in Excel 2010?

so if A1 says "Dog" A2 says "Dog" A3 says "Cat" and A4 says "Dog" A5 says "Cat" A6 says "Cat" it would highlight A1,A2, A5 and A6

Bit stumped here

 

[Jon von der Heyden]

Hi

Something like this?

Excel 2013
	A	B
1	Header	Result
2	dog	TRUE
3	dog	TRUE
4	cat	FALSE
5	dog	FALSE
6	cat	TRUE
7	cat	TRUE
8	cat	TRUE
9	dog	FALSE
10	cat	FALSE
11	dog	FALSE
12	cat	TRUE
13	cat	TRUE
14	dog	TRUE
15	dog	TRUE
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTIF(A1:A3,A2)>1

You can also use this rule in conditional formatting.

 

[GenericEric]

Hi

Something like this?
Excel 2013

	A	B
Header	Result
dog
dog
cat
dog
cat
cat
cat
dog
cat
dog
cat
cat
dog
dog

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]TRUE[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=COUNTIF(A1:A3,A2)>1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You can also use this rule in conditional formatting.

Excellent, and thank you

 

[Jenni13]

Is there a way to count only when the data repeats a certain number of times in a row? For example, I need a formula to highlight cells only if the duplicate value occurs 30 or more times consecutively.

 

[jtakw]

Is there a way to count only when the data repeats a certain number of times in a row? For example, I need a formula to highlight cells only if the duplicate value occurs 30 or more times consecutively.

Hi,

Using Jon's sample in post #2, just modify formula like:

=COUNTIF(A1:A31,A2)>29

 

[jtakw]

Hi,

Using Jon's sample in post #2, just modify formula like:

=COUNTIF(A1:A31,A2)>29

Hi,

The above won't work,

Slight modification:

This should work

=COUNTIF(A2:A31,A2)>=30

Also asked here: http://www.mrexcel.com/forum/excel-...nly-if-they-repeat-specific-number-times.html

 

[Jenni13]

Hi,
Thank you for your responses. This is closer to what I need, but =COUNTIF(A2:A31,A2)>=30 does not highlight the final 29 cells in the string of duplicates.
Any ideas how to fix this?

Thanks again!