Combinatorics in Substitutes

Question

I'd like to substitute one reoccurring string in a list of cells with several options of strings to receive a list of all possible combinations.

For better understanding I prepared a sheet for you: https://docs.google.com/spreadsheets/d/1SKvwWIE9LqM9pYZ90r68h3Sl_Bl8Keid21Y0ukokqUM/edit?usp=sharing

In this example I have a list of expressions (A2:A) that all include the word "next" which I would like to have replaced with all the available options (B2:B) to create a list of all possible combinations (C2:C)

I know that in order to create all possible combinations of A2:A and B2:B I can use: =ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9))) &" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))))

Dennis, it is best to put your data example in the message body so the question does not rely on third party services to retain its validity. I have done this for you with my edit. Hopefully my answer below also helps. — StoneGiant

StoneGiant StoneGiant · Accepted Answer · 2018-12-18T15:48:22

What you want to do is a find and replace. This does what you want:

=ARRAYFORMULA(
        REPLACE( TRANSPOSE( SPLIT( REPT( CONCATENATE(A2:A & CHAR(9)), COUNTA(B2:B) ), 
                                   CHAR(9)
                                 )
                          ), 
                 FIND( "next", TRANSPOSE( SPLIT( REPT( CONCATENATE(A2:A & CHAR(9)),
                                                       COUNTA(B2:B)
                                                     ),
                                                 CHAR(9)
                                               )
                                        )
                     ),
                 4,
                 TRANSPOSE( SPLIT( CONCATENATE( REPT( B2:B & CHAR(9), COUNTA(A2:A) ),
                                                CHAR(9)
                                              ),
                                   CHAR(9)
                                 )
                          )
               )
             )

Explanation

The phrase TRANSPOSE( SPLIT ( REPT( CONCATENATE(A2:A & CHAR(9)), COUNTA(B2:B) ), CHAR(9) )) creates a column that repeats the items in Column A as many times as there are items in column B. The pattern repeats the entire list over and over. I'll call this PHRASE1.

The phrase TRANSPOSE( SPLIT( CONCATENATE( REPT( B2:B & CHAR(9), COUNTA(A2:A)), CHAR(9) ), CHAR(9) )) creates a column that repeats the items in Column B as many times as there are items in column A. The pattern repeats each item over and over. I'll call this PHRASE2.

Now you have two arrays of equal size. The goal is to find the word next in the first array and replace it with the word in the second array. To find the word next, I used FIND("next", PHRASE1). That gives me the starting point for the replace phase. Let's call that START.

Finally, the replacement is simple: REPLACE(PHRASE1, START, 4, PHRASE2). That's what you see in the code above.

I have tested this and it works.

Combinatorics in Substitutes

1 Answers

Explanation