google-sheetsgoogle-sheets-formula

Conditional Formatting formula to highlight all numbers after first missing number in a serial number list

Google Sheet Link

Sheet Name: Conditional Formatting (Part 1):

enter image description here
(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 1: PART 2
EDIT 2: Removed PART 2 (I was told to make a different question for PART 2.

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.

Solution

  • 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