How to shuffle a list of unique items in Google Sheets?

Other Questions Google Sheets
1

I’m trying to figure out a way to mix up a list of items in Google Sheets. But here’s the catch: I need each item to show up only once in the shuffled list. For example, if I start with this:

Banana
Orange
Grape

I want it to randomly turn into something like:

Grape
Banana
Orange

The trick is, I can’t use RANDBETWEEN because it might give me duplicates. Any ideas on how to do this? I’ve been scratching my head over it for a while now. Is there a clever formula or maybe a script that could help? Thanks in advance for any suggestions!

2

I’ve encountered this challenge before, and there’s a straightforward solution using Google Apps Script. Here’s what you can do:

  1. Go to Tools > Script editor in your Google Sheet.
  2. Paste this function:

function shuffleList() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
values.sort(() => Math.random() - 0.5);
range.setValues(values);
}

  1. Save the script and close the editor.
  2. Back in your sheet, go to Tools > Macros > Import macro.
  3. Select the shuffleList function and run it.

This will shuffle your list in place. It’s efficient and works for any list length. Remember to save a copy of your original list if needed.

3

I’ve actually tackled this problem before in my work. Here’s a nifty solution using Google Sheets’ built-in functions:

  1. In column B, next to your original list, use this formula:
    =RAND()

  2. In column C, use this array formula (don’t forget to press Ctrl+Shift+Enter):
    =SORT(A1:A, B1:B, FALSE)

The RAND() function gives each item a random number. Then SORT rearranges your list based on those random numbers. This ensures each item appears once and only once in the shuffled list.

If you need to reshuffle, just recalculate the sheet (Ctrl+R on Windows, Cmd+R on Mac).

Hope this helps! Let me know if you need any clarification on the steps.

4

hey, i’ve got a quick trick for u! try this formula:

=SORT(A:A, RANDARRAY(COUNTA(A:A)))

it’ll shuffle ur list randomly. each item shows up once. no need for extra columns. just paste it in a new column and boom, shuffled list! lmk if it works for ya