Google Sheet Link
PART 1
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?
I would like to add to this formula in case where the "NUMBER" column cells contains one or more than one numbers:
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))))
=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))))