Search code examples
google-sheets

Randomizing the contents of a list in Google Sheets

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.

Solution

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

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

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