Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Unique random numbers


I want to generate 6 random numbers between 1 and 20. The problem is that the six numbers must be unique. I know I can use RANDBETWEEN to get the numbers but how do I ensure that they are unique?



Selected Answer

The first thing to understand is that "unique random numbers" aren't random. Perhaps the better description is to say that they are unpredictable.

Having disposed of the notion that you might select a random number the task turns into finding an algorithm for producting what you really want, that is an unpredictable number between 1 and 20 excluding numbers previously selected.

Since you only want 6 numbers, roughly 30% of the total pool, you might repetitively select a random number between 1 and 20 and replace it with another one if it was previously selected. Obviously, the number of failures (reserved numbers being picked at random) would increase with the perentage of required numbers from the pool, until the last number, the 20th, couldn't be random at all.

As I said, the job is to be unpredictable rather than random. Therefore any algorithm that serves that purpose will be equally suitable. Here is another suggestion.

  1. Create a list of all available numbers (1 to 20 initially) and concatenate multiple copies to create an array of, say, 50 numbers.
  2. Pick a random number between 1 and 50. Call it n.
  3. Pick the nth number from the array. That's your first number.
  4. Repeat from step 1, six times.

In this method uneligible numbers aren't even in the pool and can't therefore be drawn. Actually, I doubt that the randomness of the chosen numbers would increase if the pool were to contain 50 numbers or just 20, or even if the pool size itself were made a random number between 20 and 100. The point, as I said, is to be unpredictable, and that is a given I believe with any of these combinations.



I would use just the RAND() function then name that range and use either of the formulas below depending if going across or down

=MATCH(LARGE(Yournamerange,COLUMNS(startcell:startcellagainrelativecellref)), Yournamerange,0)

EG =MATCH(LARGE(Numbertotwenty,COLUMNS($D1:D1)),Numbertotwenty,0)


EG =MATCH(LARGE(Numbertotwenty,ROWS(D$4:D4)),Numbertotwenty,0)

This will also be dynamic as you can increase the number range and also if you require more numbers than 6 formula is dynamic. 

You can then hide the named range on another sheet and you can also format as a table so can increase the range of numbers you want to pick from to anything.

In theory you could get a duplicate rand number with such a small data set unlikely.

If you wanted to make sure that wasn't possible you could always multiply the RAND number by the ROW number in a separate table and this should eliminate this.

Hope this helps


Answer the Question

You must create an account to use the forum. Create an Account or Login