Conditional formatting consecutive duplicate and triplicate values

[kelcann]

Hello,

I'm hoping someone can help me figure out a formula. I am trying to conditionally format when the value of "1" shows up consecutively twice with one color and conditionally format it if it shows up 3 consecutive times with a different color.

Here's my example:

C4 = 1
C5 = 1
C6 = 0
C7 = 1
C8 = 1
C9 = 1

So, I would want C4 and C5 to be highlighted in yellow, and I would want C7, C8, and C9 to be highlighted in red.

I did try this:
=AND($C4<>"",$C4<>0,OR($C4=$C3,$C4=$C5))

This helps to highlight all consecutive values of "1", but it won't change the color to red when there's 3 consecutive values of "1". They just stay yellow.

Any help is greatly appreciated!

 

[Aryatect]

Hi kelcann,

I have put values like shown below starting from A1:

[TABLE="width: 56"]
<colgroup><col width="56" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]Number[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4




[/TD]
[/TR]
</tbody>[/TABLE]
Then used the below formula in cell B2 and copied it down:

=IF(A2="","",IF(SUM(A2:A4)/3=A2,3,IF(AND(A2=A1,B1=3),3,IF(SUM(A2:A3)/2=A2,2,IF(AND(A2=A1,B1=2),2,"")))))

In Case of duplicates it will return 2 and in case of triplicate it will return 3 like below:

[TABLE="width: 191"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Color[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Now you can apply the conditional formatting based on value in column C.

I hope this solves your problem.

Thanks

Hello,

I'm hoping someone can help me figure out a formula. I am trying to conditionally format when the value of "1" shows up consecutively twice with one color and conditionally format it if it shows up 3 consecutive times with a different color.

Here's my example:

C4 = 1
C5 = 1
C6 = 0
C7 = 1
C8 = 1
C9 = 1

So, I would want C4 and C5 to be highlighted in yellow, and I would want C7, C8, and C9 to be highlighted in red.

I did try this:
=AND($C4<>"",$C4<>0,OR($C4=$C3,$C4=$C5))

This helps to highlight all consecutive values of "1", but it won't change the color to red when there's 3 consecutive values of "1". They just stay yellow.

Any help is greatly appreciated!

 

[kelcann]

THANK YOU! This worked! I did add in an extra step for it to ignore 0's, but it works flawlessly. Thanks so much!!

 

[kelcann]

Hello,

I have another question. How would I update this formula to add blank rows. For example:

C4 = 1
C5 = 1
C6 = 0
C7 = 1
C8 = 1
C9 = 1
C10 = 0
C11 = 1
C12 = 1
C13 = (Cell is blank)
C14 = 1

So, I would want C4:C5 to be highlighted in yellow, C7:C9 to be highlighted in red, and C11, C12, and C14 to be highlighted in red.

I hope this makes sense!

Thanks!