# Random Sorted Order without Repeats Using Excel Formulas

There might be times when you need to shuffle a list of distinct items. It can be to run a simulation analysis, to pick a random sample for statistical analysis or to just have some fun. All you have to do is generate random integers between 1 and the size of your list. And then rearrange the list using the INDEX function. Simple, right? No, there’s a problem with this approach: random numbers could repeat!

As you would’ve guessed by now, we are here to present a solution which does NOT involve VBA. Excel MVP Jordan Goldmeier is here to ensure Excel fanatics sleep peacefully tonight.

## 1 – Numbering

The first step is to number your list of distinct items starting from 1. Let’s say we have a list of size 5.  Then you would number them from 1 to 5. This numbering will help us reorder the list towards the end. The image on the right illustrates this.

## 2 – Generating Random Numbers

This is the fun step. Normally we would generate numbers between 1 and the size of your list using the RANDBETWEEN function. But that is where the repetition occurs, as shown in the image on the right.

To avoid it, we will add a very small number to each random integer we generate. But these “small” numbers must change as the row number changes to make the overall sum unique.

The way to achieve this is to use the following formula:

=RANDBETWEEN(1, list_size) + ROW()/10000

ROW just returns the row number, which is obviously unique for each row. The division by 10,000 makes sure that this number is small. The overall result should look something like the image below.

## 3 – Ranking In Excel

You may have observed that the random numbers are unique now. All we have to do is to rank them. We can use the formula RANK to rank the random numbers. Since the formula takes in the column array of random numbers, you can lock it by pressing F4 to copy the formula effortlessly. The image on the right illustrates this step.

## 4 – The Final Step

This is where you get to reap the result of all your efforts. You can use the ranking to get your shuffled list using the INDEX function. The array would be your original list and the row number will be the rank in that column. Do not forget to lock the array reference to ease copying the formula. The overall result should look something like this:

Just one thing to note here: although 10,000 was selected randomly to add “small” numbers to the random numbers, you have to make sure whichever number you select is larger than your list size.

Tags

#### You may also like

December 20, 2013

## 100+ Best Excel Resources And Websites

100+ Best Excel Resources And Websites

August 6, 2019

## How to Create a Relative File Path in Power Query

How to Create a Relative File Path in Power Query

December 20, 2013

## 100+ Best Excel Books – Learning Spreadsheets

100+ Best Excel Books – Learning Spreadsheets

April 9, 2011

## How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

## The Excel Rollover Mini FAQ

The Excel Rollover Mini FAQ

February 17, 2014

## It’s time to say “Goodbye,” to Hungarian Notation

It’s time to say “Goodbye,” to Hungarian Notation
• George says:

Your random numbers using the row will favor the lower cards, you could do another random -1 or 1 to determine sort order but it will still distribute unevenly favoring both tails.