sortinggoogle-sheetsfilterlambdagoogle-sheets-formula

Formula to find missing serial numbers


Google Sheet Link
PART 1

enter image description here

INPUT:

STUDIO FIN YEAR NUMBER
A X-X 3
A X-X 1
A O-O 8
B X-X 1
B X-X 2
C X-X 1
B O-O 4
B X-X 3
A X-X 5
C X-X 2
C X-X 3
A O-O 1
A O-O 2
A O-O 2
A O-O 4
C O-O 1
C O-O 1

OUTPUT: (In a single cell)
MISSING NUMBERS:

A: (O-O): 3,5,6,7 (X-X): 2,4
B: (O-O): 1,2,3

Explanation:

For "STUDIO" A in "FIN YEAR" X-X there are numbers 2,4 missing, similarly for O-O 3,5,6,7.
For "STUDIO" B in "FIN YEAR" X-X there is no number missing but for O-O 1,2,3 (So only (O-O) is displayed).
For "STUDIO" C in "FIN YEAR" X-X & O-O there is no missing number. (So C: is not displayed).

NOTES:

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.

How can I write formula to achieve the given output?

Part 2

I would like to add to this formula in case where the "NUMBER" column cells contains one or more than one numbers:

enter image description here


Solution

  • Maybe this helps:

    =ARRAYFORMULA(LET(a, A2:A20, b, B2:B20, c, C2:C20, 
     p, "max(Col3) where Col3>1 group by Col1,Col2 label max(Col3)''", 
     q, "select max(Col2) group by Col2 pivot Col1",
     f, FLATTEN(INDEX(QUERY({a&":đ("&b&"):¤×", {b,c}}, "select Col1,"&p, ),, 1)&
     BYROW(QUERY({a,b,c}, "select "&p, ), LAMBDA(c, MAKEARRAY(1,c,LAMBDA(c,r,r))))), 
     SUBSTITUTE("MISSING NUMBERS:Đ"&TEXTJOIN(, 1, "Đ"&TRIM(QUERY(QUERY(SPLIT(SUBSTITUTE(
     REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(SPLIT(QUERY(FILTER(f, 
     NOT(COUNTIF({a&":đ("&b&"):¤×"&c}, f))), "where not Col1 ends with '×'", )&",", "×"), 
     q),,9^9))), "\s|,$", ), "¤", " "), "đ"), q),,9^9))), "Đ", CHAR(10))))
    

    enter image description here


    Update:

    =ARRAYFORMULA(LET(z, A2:C20, i, SPLIT(TRANSPOSE(SPLIT(JOIN(,BYROW(FILTER(z, INDEX(z,,1)<>""), 
     LAMBDA(r,JOIN(",",REDUCE(";",r,LAMBDA(c,cc,FLATTEN(FLATTEN(c)&","&SPLIT(cc,",")))))))),";,",)),","), 
     a, INDEX(i,,1), b, INDEX(i,,2), c, INDEX(i,,3), 
     p, "max(Col3) where Col3>1 group by Col1,Col2 label max(Col3)''", 
     q, "select max(Col2) group by Col2 pivot Col1",
     f, FLATTEN(INDEX(QUERY({a&":đ("&b&"):¤×", {b,c}}, "select Col1,"&p, ),, 1)&
     BYROW(QUERY({a,b,c}, "select "&p, ), LAMBDA(c, MAKEARRAY(1,c,LAMBDA(c,r,r))))), 
     SUBSTITUTE("MISSING NUMBERS:Đ"&TEXTJOIN(, 1, "Đ"&TRIM(QUERY(QUERY(SPLIT(SUBSTITUTE(
     REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(SPLIT(QUERY(FILTER(f, 
     NOT(COUNTIF({a&":đ("&b&"):¤×"&c}, f))), "where not Col1 ends with '×'", )&",", "×"), 
     q),,9^9))), "\s|,$", ), "¤", " "), "đ"), q),,9^9))), "Đ", CHAR(10))))
    

    enter image description here