Cartesian Product with constraints

Question

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).

i can't share the database but my problem is that i have to match one value with two other values like if P1 was "cat" , F1 was the verb, and C1 is the object. my problem is that i have to form all the possible phrases that matches with "cat" adding F1 and P1. All the results have to be in different rows (like in the example below). — Saem, Feb 5 at 21:05
My real problem is that i can't just match all the P with all the F and all the C (because I would just do PFC) but P doesn't mach with all the F and with all the C. Every P matches with at least one F and one C but for every P there are something like 1500 P and 100F and 100C i can't do all the matches by hand, i was hoping to find a formula to solve it. — Saem, Feb 5 at 21:14
no the problem was exactly that i can't do like this P,F,C are always in the same position — Saem, Feb 5 at 21:16
all the matches have to follow a schedule as you can see in the table up there — Saem, Feb 5 at 21:17
not ALL possible combos, but all the combos that fit with my rules (for example: Does P1 match with F1?And with C1?==>yes?= P1+F1+C1==>No?=P1+F(the one that matches)+C(the one that matches)) — Saem, Feb 5 at 21:30

Erik Tyler · Accepted Answer · 2019-02-06 00:28:41Z

0

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.

edited Feb 6 at 0:28

answered Feb 6 at 0:22

Erik Tyler

7991 silver badge6 bronze badges

Thank you so much Erik! It works perfecly and fit great to what i want to do! – Saem Feb 6 at 4:52
Happy to help, Saem. If you would, please mark my formula as the correct answer. It helps the community here see at a glance which issues have been fully addressed, so that others don't continue to try to add suggestions unnecessarily. Thanks. – Erik Tyler Feb 6 at 5:44
Hi Erik, actually i have some issues ; i'm running this code =ArrayFormula({SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("\", TRUE, IF($B$2:$CA$425="x", REPT($A$2:$A$425&" "&$B$1:$CA$1&"\", MMULT(N($CC$2:$HB$425="x"), TRANSPOSE(COLUMN($CC$1:$HB$1)^0))), "")), "\")), " "), TRANSPOSE(SPLIT(JOIN(" ", REPT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF($CC$2:$HB$425="x", $CC$1:$HB$1, "")), , COLUMNS(IF($CC$2:$HB$425="x", $CC$1:$HB$1, "")))))&" ", MMULT(N($B$2:$CA$425="x"), TRANSPOSE(COLUMN($B$2:$CA$425)^0)))), " "))}) that is exactly like your but it gives me a "Function Array_Row has mismatched row size" – Saem Feb 11 at 9:52
First thing I'd suggest is taking out all of the dollar signs. Beyond that, I'd need to see it in context. Feel free to share a link with editing permissions. – Erik Tyler Feb 11 at 18:25
docs.google.com/spreadsheets/d/… i did as you said, feel free to edit and comment i'll be happy to learn more. there are 425 "P" , all the "F" are the datas before CA and all the datas after CC are the "C". the formula was in HC2 cell – Saem Feb 11 at 20:45

| show 3 more comments

user0 · Accepted Answer · 2019-02-25 11:07:58Z

step 1:

convert the dataset to an actual value holder
```
=ARRAYFORMULA(IF(C3:J5="x", $C$2:$J$2, ))
```

step 2:

multiply combinations between P and F values

drag formula over to the E column

=ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$9:$B$11&CHAR(9)), COUNTA(C9:C11)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(C9:C11&CHAR(9), COUNTA($B$9:$B$11))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$9:$B$11&" "&C9:C11}, 1, 0), ))

step 3:

multiply combinations between P&F and C values
drag formula over to the J column

repeat logic for each P&F value (eg. G18 and G22)

=ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($C$14:$C$17&CHAR(9)), COUNTA(G9:G11)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(G9:G11&CHAR(9), COUNTA($C$14:$C$17))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$C$14:$C$17&" "&G9:G11}, 1, 0), ))

step 4:

extract all combinations and split them into separate cells

=ARRAYFORMULA(SPLIT(QUERY({G14:G25; H14:H25; I14:I25; J14:J25}, 
 "select * where Col1 is not null order by Col1 asc", 0), " "))

spreadsheet demo

step 5: (only for hardcore long-formula-lovers)

basically all of it can be packed up in one-cell solution

=ARRAYFORMULA(SPLIT(QUERY({
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not Null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,1)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,2)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,5)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,6)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&" "&
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,7)}, 1, 0), ));
 ARRAYFORMULA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&CHAR(9)),
 COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8)&CHAR(9),
 COUNTA(IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {IFERROR(VLOOKUP(IFERROR(QUERY(ARRAYFORMULA(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE($B$3:$B&CHAR(9)), COUNTA(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)&CHAR(9), COUNTA($B$3:$B))), CHAR(9))))),
 "select Col1 where Col1 is not null", 0), ), {$B$3:$B&" "&INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,3)}, 1, 0), )&" "& 
 INDEX(IF($C$3:$J="x", $C$2:$J$2, ), ,8)}, 1, 0), ))}, 
 "select * where Col1 is not null order by Col1 asc", 0), " "))

spreadsheet demo v2

thank you for your time ! and a really impressive job but Erik's solution is smaller and easier to appply! — Saem, Feb 6 at 4:55
hi Mark, how do you think that you answer would fit in a situation like this? how could you adapt it? docs.google.com/spreadsheets/d/… feel free to edit — Saem, Feb 14 at 4:31
as you can read in the comment upside erik solution is smaller but limited by textjoin 50k characters (and as you can see i have way more) I passed over this problem changing all the words in numbers, sort them and then change them back again. This solution actually is quite heavy so I'm looking for something smaller and that can be done in one string. And more important i need no issues if i have to add more things to the chart. — Saem, Feb 14 at 6:01
thank you ! iactually the solution i find is that i changed all the words with numbers report the result there like 1-35-55, and then do a LOOKUP to check the number corrispondence and change the number in a word all this obviously with other sheet but the problem is that is quite heavy! — Saem, Feb 14 at 6:50
they are all the data i have for now but they can potentially be the double (this is the reason I need flexibility) — Saem, Feb 14 at 12:06

Stack Exchange Network

current community

your communities

more stack exchange communities

Cartesian Product with constraints

2 Answers 2

step 1:

step 2:

step 3:

step 4:

spreadsheet demo

step 5: (only for hardcore long-formula-lovers)

spreadsheet demo v2

Your Answer

Not the answer you're looking for? Browse other questions tagged google-sheets formulas google-sheets-query google-sheets-arrayformula vlookup or ask your own question.

Hot Network Questions

2 Answers 2

step 1:

step 2:

step 3:

step 4:

step 5: (only for hardcore long-formula-lovers)

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged google-sheets formulas google-sheets-query google-sheets-arrayformula vlookup or ask your own question.

Related