Conditional if in multiple rows and columns

[alecambo]

Greetings! I have a question that I believe it should be fairly easy to resolve for someone with experience. I have a data set that looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Run[/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Sequence[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-[/TD]
[/TR]
[TR]
[TD]SODA[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SODA[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]SODA[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SODA[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, the order always remains constant for each Run (1, 2, 3, 4), and the Runs always consist of 4 steps. What is variable is the sequence, which can take any of the following values: "-", "1", "2", "3", "4".

That being said, I need a formula that highlights the 4 "Run" cells in red when the sequence for that Run is any different from the two following sequences: "2 ,1, 3, 4" or "2, 1, 3, -":
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Run
[/TD]
[TD="align: center"]Order
[/TD]
[TD="align: center"]Sequence
[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



For instance, this would be an invalid Run since it doesn't follow any of the two admitted sequences "2 ,1, 3, 4" or "2, 1, 3, -".[TABLE="width: 500"]
<tbody>[TR]
[TD]Run[/TD]
[TD]Order[/TD]
[TD]Sequence[/TD]
[/TR]
[TR]
[TD]SODA
[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]SODA
[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]SODA
[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]SODA
[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I appreciate any help you may provide. Thanks in advance,

Ale

 

[Oaktree]

If you're using Excel 2016 (when the TEXTJOIN function was introduced) or later:

1) select cells B3 through B14 (or whatever the end of your range is).

2) Go to conditional formatting -- > new rule

3) Select "use a formula to determine which cells to format"

4) in the "Format values where this formula is true" box, enter this (adjusting the $B$14 and $D$14 to be the end of your ranges as necessary):

=NOT(OR(TEXTJOIN("|",,IF($B$3:$B$14=B3,$D$3:$D$14,""))="2|1|3|4",TEXTJOIN("|",,IF($B$3:$B$14=B3,$D$3:$D$14,""))="2|1|3|-"))

5) Click "Format" and change the Font color (on the font tab) to be red.

6) Click OK twice.

 

[Eric W]

Another option which doesn't require TEXTJOIN.

I'm assuming that the "Run" column is A, the "Sequence" column is C, and there is 1 header row, so the first row of data is 2. Given that, select column A, click Conditional Formatting > New Rule > Use a formula > and enter:

=IF(A1="",0,IF(INDEX(C:C,ROW()-MOD(ROW()-2,4))&INDEX(C:C,ROW()-MOD(ROW()-2,4)+1)&INDEX(C:C,ROW()-MOD(ROW()-2,4)+2)="213",0,1))

Click Format... and choose your fill color.

 

[alecambo]

Thank you both for posting these answers. Right now I'm away from my work computer but I'm going to test both answers as soon as I can.

Best,
Ale

 

[alecambo]

I've tried both answers and they seem to work just fine.

If anyone else is interested on sharing their take on the matter, I'll gladly accept it.

Thanks,
Ale