2

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
8
  • 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 '19 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 '19 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 '19 at 21:16
  • all the matches have to follow a schedule as you can see in the table up there – Saem Feb 5 '19 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 '19 at 21:30
1

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
8
  • Thank you so much Erik! It works perfecly and fit great to what i want to do! – Saem Feb 6 '19 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 '19 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 '19 at 9:52
  • 1
    Saem, the TEXTJOIN function in Google Sheets has a limit of 50000 characters that it can hold at any one time. You are trying to process a huge amount of information, which is exceeding that 50000-character limit. So before the first part of the entire formula is even done, an error is being thrown (resulting in one cell's worth of "data": the error). That means the second half of the formula, which includes over 6700 results, has nothing to match with. – Erik Tyler Feb 12 '19 at 4:24
  • 1
    thank you erik for your time and patience, it is the first time to me here so thant you also for the "good things to do" lesson. I saw the problem of the 50k textjoin parameter but i thought i could be something manually upgradable (as the loop times paramether). do you think the other answer can be better? i thought i was a little less flexible – Saem Feb 12 '19 at 4:37
2

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
7
  • 1
    thank you for your time ! and a really impressive job but Erik's solution is smaller and easier to appply! – Saem Feb 6 '19 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 '19 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 '19 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 '19 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 '19 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.