google-sheets

Randomize cells in Google Sheets


Is there a formula to randomize a column of data which keeps each item represented only once (has the same items)?

So:

APPLES 
PEARS
BERRIES

Might come out as

PEARS
BERRIES
APPLES

Randbetween formulas no good here, as you might get two 'PEAR's.


Solution

  • There is a new "randomize range" feature available in the context menu after selecting a range:

    randomize]


    The following approach implements the idea of pnuts, but without creating a column filled with random numbers:

    =query({A2:A20, arrayformula(randbetween(0, 1e20 + row(A2:A20)))}, "select Col1 order by Col2", 0)
    

    Here A2:A20 is the range to be permuted. The arrayformula generates a random integer for each. The query sorts the array by those random integers, but does not put the random numbers in the spreadsheet.

    The entropy of randbetween is 64 bits, so collisions are extremely unlikely. And even if two random numbers happen to be equal, that will not generate repetitions; sorting by whatever column never does that. It only means the corresponding pair of entries will appear in their original order.