0

I have a Google Sheet and I want to generate all the possible permutations of a keyword (KW).

Eg: I have a SpreadSheet

Outcome: I.*have.*a|I.*a.*have|have.*I.*a|have.*a.*I|a.*have.*I|a.*I.*have

Issue, if the keyword is more then 4 words, i'm unable to generate the 4th+ word in the KW as seen above (Missing "SpreadSheet").

Currenlty this is the formula that is able to work upto 3 words.

=IFS(B5=1,B4,B5=2,query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col1"), B5>=3, query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2"))

Example in Google Sheet

I tried adding the 4th word but gotten an error

"Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <INTEGER_LITERAL> "4 "" at line 1, column 12. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... "+" ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... "+" ... "-" ..."

The 4th formula. =IFS(B5=1,B4,B5=2,query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col1"), B5=3, query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2"), B5>=4, query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&".*"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&".*"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&"|"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&".*"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&".*"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&"|"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&".*"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&".*"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&"|"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 4")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&"|"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&"|"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&"|"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&"|"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&".*"&query(SPLIT(B4, " "), "SELECT Col3")&".*"&query(SPLIT(B4, " "), "SELECT Col 1")&"|"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&".*"&query(SPLIT(B4, " "), "SELECT Col1")&".*"&query(SPLIT(B4, " "), "SELECT Col 2")&"|"&query(SPLIT(B4, " "), "SELECT Col4")&".*"&query(SPLIT(B4, " "), "SELECT Col 3")&".*"&query(SPLIT(B4, " "), "SELECT Col2")&".*"&query(SPLIT(B4, " "), "SELECT Col 1"))

Is there a way I can generate the 4th or more KW variant or maybe with something simpler that will input from 1 cell and output into 1 cell?

Thanks.

Jon97
  • 1

1 Answers1

0

see: https://stackoverflow.com/a/74160711/5632629

try:

enter image description here

where A3:

=FLATTEN(SPLIT(A1, " "))

C3:

=LAMBDA(z, LAMBDA(x, y, INDEX(QUERY(FLATTEN(IF((x<y)+(x>y), x&".*"&y, )), 
 "where Col1 is not null", )))(z, TRANSPOSE(z)))(A3:INDEX(A3:A6, COUNTA(A3:A6)))

D3:

=LAMBDA(x, y, INDEX(QUERY(FLATTEN(IF(REGEXMATCH(x, y),, x&".*"&y)), 
 "where Col1 is not null", )))(C3:INDEX(C3:C, COUNTA(C3:C)), 
 TRANSPOSE($A3:INDEX($A3:$A14, COUNTA($A3:$A14))))

E3:

=LAMBDA(x, y, INDEX(QUERY(FLATTEN(IF(REGEXMATCH(x, y),, x&".*"&y)), 
 "where Col1 is not null", )))(D3:INDEX(D3:D, COUNTA(D3:D)), 
 TRANSPOSE($A3:INDEX($A3:$A14, COUNTA($A3:$A14))))

G3:

=TEXTJOIN("|", 1, E3:E)
player0
  • 124,011
  • 12
  • 67
  • 124