How to shuffle list items randomly in Google Sheets

Other Questions Google Sheets
1

I need help shuffling words in two different formats in Google Sheets.

Format 1: Words in one cell separated by commas like C6: planning, innovative, strategy, effective, results.

Format 2: Same words but each in separate cells like J7: planning, M7: innovative, P7: strategy, S7: effective, U7: results.

The cells aren’t next to each other for layout reasons.

I want to create a formula that automatically generates random word orders from preset lists. I have many lists to process so manual shuffling won’t work.

My current setup:
I’m comparing two similar sentences in Google Sheets. When words don’t match, I use a formula like =if(H4 <> H5, H4, “”) to show the correct word.

Example:
A4: The planning required innovative strategy for effective results.
A5: The _____ required _____ _____ for _____ _____.

My formulas:
G4: =split(REGEXREPLACE(A4, “[^A-Za-z ']”, “”)," ")
G5: =split(REGEXREPLACE(A5, “[^A-Za-z _']”, “”)," ")
G6: =if(G4 <> G5, G4, “”)

This gives me: J7: planning - M7: innovative - P7: strategy - S7: effective - U7: results.

Using C4: =TEXTJOIN(", ", TRUE, G6:Y6) I get: planning, innovative, strategy, effective, results.

But I need these words in random order each time. How can I shuffle them?

2

You can do this by mixing your current logic with SEQUENCE and RANDARRAY for randomization. For the comma-separated format, swap your C4 formula with: =TEXTJOIN(", ", TRUE, INDEX(FILTER(G6:Y6, G6:Y6<>""), SORT(RANDARRAY(COUNTA(FILTER(G6:Y6, G6:Y6<>""))), SEQUENCE(COUNTA(FILTER(G6:Y6, G6:Y6<>"")))))) This generates random numbers to shuffle your filtered words. For individual cells, set up a helper range (like AA1:AB5) - put your words in column AA and use RAND() in column AB. Then use SMALL and MATCH functions to pull the randomized order into J7, M7, P7. This works well because it plugs right into your existing word extraction without breaking your current formulas.

3

Just use SORT with RAND() to randomize your extracted words. For the comma-separated format in C4, try this: =TEXTJOIN(", ", TRUE, SORT(FILTER(G6:Y6, G6:Y6<>""), RANDARRAY(COUNTA(FILTER(G6:Y6, G6:Y6<>""))))) It filters out empty cells first, then sorts them using random numbers from RANDARRAY. COUNTA makes sure you get the right number of random values. For format 2 with individual cells, you’ll need helper columns. Make one column with your filtered words, add another with =RAND() next to each word, then use INDEX with SORT to pull the randomized words into J7, M7, P7, etc. Heads up - this recalculates every time the sheet updates. If you want static results, copy and paste values after each shuffle.

4

Wrap your formula with SORTBY and RANDARRAY. For the comma version, change C4 to: =TEXTJOIN(", ", TRUE, SORTBY(FILTER(G6:Y6, G6:Y6<>""), RANDARRAY(COUNTA(FILTER(G6:Y6, G6:Y6<>""))))) This works well and doesn’t mess with your current setup. Individual cells are trickier - you’ll probably need a pivot table or just reference the shuffled comma list and split it.