I want to randomize either/or of the following:

1. A string that contains words separated by a column (e.g., E4: complexity, her, approach, seamless, execution), or

2. A range of cells that contain the same words but in a different cell each (e.g., H5: complexity - L5: her - N5: approach - Q5: seamless - R5: execution)

The reason why the cells are not adjacent to each other is trivial.

The formula's goal should be to automize a random order generation of words given a predetermined list for many lists (i.e., why I don't want to do it manually).

If you want more context, using Google Sheets, I compare two near-identical sentences. If there are mismatches in the words, a formula (e.g., =if(G3 <> G4, G3, "")) shows which word should be the correct one.

Example:

B3: Despite the complexity of the task, her meticulous approach ensured a seamless execution.

D3: Despite the _____ of the task, _____ meticulous _____ ensured a _____ _____.

F3: =split(REGEXREPLACE(B3, "[^A-Za-z ']", "")," ")

F4: =split(REGEXREPLACE(D3, "[^A-Za-z _']", "")," ")

F5: =if(F3 <> F4, F3, "")

Output:

H5: complexity - L5: her - N5: approach - Q5: seamless - R5: execution

The cells in between are empty.

E3: =TEXTJOIN(", ", TRUE, F5:Z5)

Now, TEXTJOIN will give me an output in order (e.g., complexity, her, approach, seamless, execution) but I want it in a random order.

I want to randomize either/or of the following:

1. A string that contains words separated by a column (e.g., E4: complexity, her, approach, seamless, execution), or

2. A range of cells that contain the same words but in a different cell each (e.g., H5: complexity - L5: her - N5: approach - Q5: seamless - R5: execution)

The reason why the cells are not adjacent to each other is trivial.

The formula's goal should be to automize a random order generation of words given a predetermined list for many lists (i.e., why I don't want to do it manually).

If you want more context, using Google Sheets, I compare two near-identical sentences. If there are mismatches in the words, a formula (e.g., =if(G3 <> G4, G3, "")) shows which word should be the correct one.

Example:

B3: Despite the complexity of the task, her meticulous approach ensured a seamless execution.

D3: Despite the _____ of the task, _____ meticulous _____ ensured a _____ _____.

F3: =split(REGEXREPLACE(B3, "[^A-Za-z ']", "")," ")

F4: =split(REGEXREPLACE(D3, "[^A-Za-z _']", "")," ")

F5: =if(F3 <> F4, F3, "")

Output:

H5: complexity - L5: her - N5: approach - Q5: seamless - R5: execution

The cells in between are empty.

E3: =TEXTJOIN(", ", TRUE, F5:Z5)

Now, TEXTJOIN will give me an output in order (e.g., complexity, her, approach, seamless, execution) but I want it in a random order.

• google-sheets

2 Answers 2

Use sort() and randarray(), like this:

=textjoin(", ", true, sort(tocol(F5:Z5), randarray(columns(F5:Z5)), true))

See sort(), tocol() and randarray().

You can try this approach

What I did is manually declared the range involved in the randomization, attach it to random values beside it and sort it. It will create randomization using the random values produced by Randarrays. You can apply the Join function to join them in I don't understand the last part of your requirement yet however I understand that you want to just randomize it.

Formula

=LET(x, TOCOL({H3,L3,N3,Q3,R3}), y, RANDARRAY(ROWS(x),1), z, HSTACK(y,x), JOIN(",",CHOOSECOLS(SORT(z, CHOOSECOLS(z,1),true),2)))

Note: Please make sure that the range will be adjusted accordingly.

Result:

Resulting string:

seamless,complexity,execution,her,approach

It is also gonna be different in your end since each time the formula is randomized then new values will be given hence new randomization.

Reference:

Sort

Randarray