1

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

|improve this question
  • 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
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.

|improve this answer
  • 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
1

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), " "))

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), " "))

0

|improve this answer
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.