December 16

Random Sorted Order without Repeats Using Excel Formulas

1  comments

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.

So, let’s see what Jordan has in the bag for us.

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.1

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.

2

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

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

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:

5

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.

Get the download


Tags


You may also like

51: Oz du Soleil & the Global Excel Summit 2021

51: Oz du Soleil & the Global Excel Summit 2021

Global Excel Summit 2021

Global Excel Summit 2021
  • 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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

    >