Google Sheets – Cartesian Product with Constraints

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I have a really big database that I have to sort and divide as the example below:

            F1  F2  F3      C1  C2  C3  C4      results         

    P1      X       X       X   X   X           P1  F1  C1  

    P2      X       X           X       X       P1  F3  C1  

    P3          X           X       X           P1  F1  C2  

                                                P1  F3  C2  

                                                P1  F1  C3  

                                                P1  F3  C2  

                                                P2  F1  C2

I think it has something to do with Cartesian product but I really don't know how to give some sort of constraints (the X are the one that matches).

Best Answer

Saem, assuming your label "P1" is in A2, your label "F1" is in B1, and your label "C1" is in F1, the following array formula should give the results you want:

=ArrayFormula({SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("\", TRUE, IF(B2:D4="x", REPT(A2:A4&" "&B1:D1&"\", MMULT(N(F2:I4="x"), TRANSPOSE(COLUMN(F1:I1)^0))), "")), "\")), " "), TRANSPOSE(SPLIT(JOIN(" ", REPT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(F2:I4="x", F1:I1, "")), , COLUMNS(IF(F2:I4="x", F1:I1, "")))))&" ", MMULT(N(B2:D4="x"), TRANSPOSE(COLUMN(B2:D4)^0)))), " "))})

You should be able to have a scope of any size and just replace the corresponding ranges to get a complete list.

Related Topic