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:

Number
1
1
0
1
1
1
2
4
4
4

<colgroup><col width="56" style="width:42pt"> </colgroup><tbody>
</tbody>

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:

Number	Color
1	2
1	2
0
1	3
1	3
1	3
2
4	3
4	3
4	3

<colgroup><col><col></colgroup><tbody>
</tbody>

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!