Search code examples
google-sheetsgoogle-sheets-formula

Conditional Formatting formula to highlight all numbers after first missing number in a serial number list where one cell can contain multiple values


Google Sheet Link

Sheet Name: Conditional Formatting (Part 2):

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 7 so after 7 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.
One cell can have multiple values.

TABLE:

STUDIO FIN YEAR NUMBER (OUTPUT) CONDITIONAL FORMATING
A X-X 3,4
A X-X 5,8 TRUE
A X-X 1
A O-O 8 TRUE
A X-X 6
B X-X 1
B X-X 2
C X-X 1
B O-O 4 TRUE
B X-X 3
A X-X 2 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

Please do not mark this as duplicate question of PART 1, because I was asked to create a separate question for this PART 2.


Solution

  • Here's one approach you may test out:

    🌀The formula is applied at column_D & then conditional format applied to columns A,B,C since such computational-heavy formulas when used directly as a conditional formatting rule can extremely slow down the sheet.

    =let(Ξ,map(let(z,sort(unique(A2:A&B2:B)),filter(z,z<>"")),lambda(Λ,hstack(Λ,let(x,split(join(",",filter(C:C,A:A&B:B=Λ)),","),y,sequence(1,max(x)),ifna(min(filter(y,isna(xmatch(y,x))))))))),
     map(A2:A,B2:B,C2:C,lambda(a,b,c,if(counta({a,b,c})<>3,,let(Σ,index(xmatch(vlookup(a&b,filter(Ξ,index(Ξ,,2)<>""),2,),split(c,","),1)),if(isnumber(Σ),true,))))))
    

    enter image description here