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:
<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, -":
<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, -".
<tbody>
</tbody>
I appreciate any help you may provide. Thanks in advance,
Ale
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