CODEWITHSUNDEEP
google-sheets
tardy pigeon
tardy pigeon

Reputation: 225

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.

Upvotes: 2

Views: 7226

Answers (4)

pumpkinslayer
pumpkinslayer

Reputation: 13

This formula outputs the randomized data from a single-column input range.

=CHOOSECOLS(SORT(HSTACK(DataArray, RANDARRAY(ROWS(DataArray),1)),2,TRUE),1)

From inside out...

  1. RANDARRAY creates a 1-column array of random numbers with the same size as the input data.
  2. HSTACK creates a 2-column array with the DataArray as column 1 and the random number array as column 2.
  3. SORT sorts the 2-column array by the random numbers.
  4. CHOOSECOLS outputs the first column.

Upvotes: 0

astronomyfortwo
astronomyfortwo

Reputation: 11

Came across this while looking for a formula to generate a set of random unique integers and ended up devising my own, so I'm leaving it here for anyone else looking for the same:

=SORT(SEQUENCE(A$1),RANDARRAY(A$1),FALSE) where A$1 is the count of integers to generate (expressed here as a cell reference because I like to create sheets where I can input a number in a cell rather than changing the formula, but this can of course be just a number.)

This can be expanded by adding the three other fields to SEQUENCE as explained in the function's documentation, or by wrapping it in an ARRAYCONSTRAIN to limit the count of entries returned without changing the minimum or maximum values of the generated entries. Hope all this makes sense!

Upvotes: 1

MattyUSA42
MattyUSA42

Reputation: 11

I adopted a similar approach to user6655984 before I found this post.

RANDARRAY seemed to be a neat call once solution.

I had similar demands. Formula based, randomized return order, ability to have only unique records or not as the whim took me.

Right clicking to randomize range meant user interaction I didn't want and the data is dynamic.

I built in the random numbers into a query data range on the fly.

I get the flexibility of query (can easily expand the range, add returned columns filter criteria etc), I don't have to show the random numbers at all and can wrap it in UNIQUE if desired, it re-randomizes with each recalc.

Have some data in column A2:A.

To see the inline data range. ={RANDARRAY(ROWS($A$2:$A)),$A$2:$A}

Query (inc duplicates), filter out empty. =QUERY({RANDARRAY(ROWS($A$2:$A)),$A$2:$A},"SELECT Col2 WHERE COL2<>'' ORDER BY Col1 ",0)

Same but wrapped by unique. =UNIQUE(QUERY({RANDARRAY(ROWS($A$2:$A)),$A$2:$A},"SELECT Col2 WHERE COL2<>'' ORDER BY Col1 ",0))

Hope it helps someone, even if years later. :)

Matt

Upvotes: 0

user6655984
user6655984

Reputation:

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.

Upvotes: 3

Related Questions