Conditional Formatting formula to highlight all numbers after first missing number in a serial number list
PART1:
(Rows which meets conditional formatting are highlighted RED)
Explanation:
For "STUDIO" A in "FIN YEAR" X-X first missing number is 2 so after 2 all number in A in X-X would be red, similarly for O-O first missing number is 3 so after 3 all numbers in A in O-O would be red.
For "STUDIO" B in "FIN YEAR" O-O first missing number is 1 so after 1 all number in B in O-O would be red.
QUESTION: I want a formula for conditional formatting which gives TRUE for all the numbers after the first missing number for each "STUDIO" in each "FIN YEAR". (As shown in above image)
NOTE: Numbers start from 1 for each "FIN YEAR" for each "STUDIO". Numbers can repeat any number of time. Every input is random, there is no order for any columns.
TABLE:
| STUDIO | FIN YEAR | NUMBER | (OUTPUT) CONDITIONAL FORMATING |
|---|---|---|---|
| A | X-X | 3 | TRUE |
| A | X-X | 1 | |
| A | O-O | 8 | TRUE |
| B | X-X | 1 | |
| B | X-X | 2 | |
| C | X-X | 1 | |
| B | O-O | 4 | TRUE |
| B | X-X | 3 | |
| A | X-X | 5 | TRUE |
| C | X-X | 2 | |
| C | X-X | 3 | |
| A | O-O | 1 | |
| A | O-O | 2 | |
| A | O-O | 2 | |
| A | O-O | 4 | TRUE |
| C | O-O | 1 | |
| C | O-O | 1 |
EDIT:
PART 2:
Please give a conditional formatting formula in case where the "NUMBER" column cells contains one or more than one numbers?
Please do not mark this question as duplicate of my this question, since it's not. That one is asking the Formulas to calculate the missing numbers, whereas this question is asking the conditional formatting formula to highlight the numbers after a missing number.
Answers
EDIT: Updated formula
Try the following conditional formatting custom formula applied to A2:C
=ARRAYFORMULA(IFNA(
VLOOKUP($A2&$B2,MAP(UNIQUE($A:$A&$B:$B),LAMBDA(c,LET(
f,FILTER($C:$C,$A:$A&$B:$B=c),
s,ROW(INDIRECT("1:"&MAX(f))),
{c,MIN(FILTER(s,0=COUNTIF(f,s)))}))),2,0),MAX($C:$C)))<$C2
