Conditional formatting consecutive duplicate and triplicate values

kelcann

New Member
Joined
Jun 19, 2019
Messages
3
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:

Code:
=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!
 
Upvote 0
THANK YOU! This worked! I did add in an extra step for it to ignore 0's, but it works flawlessly. Thanks so much!!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top