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:

Run	Order	Sequence
CAR	1	2
CAR	2	1
CAR	3	3
CAR	4	4
APPLE	1	2
APPLE	2	1
APPLE	3	3
APPLE	4	-
SODA	1	1
SODA	2	4
SODA	3	3
SODA	4	2

<tbody>
</tbody>

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, -":

Run	Order	Sequence
CAR	1	2
CAR	2	1
CAR	3	3
CAR	4	4
APPLE	1	2
APPLE	2	1
APPLE	3	3
APPLE	4	-

<tbody>
</tbody>

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, -".

Run	Order	Sequence
SODA	1	3
SODA	2	4
SODA	3	1
SODA	4	2

<tbody>
</tbody>

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