Generating Unique Random Numbers in an interval in Excel

MS Excel has an inbuilt function i.e. RANDBETWEEN which returns a random number between the numbers you specify. The random numbers that this function generates can be repetitive.I found two ways to guarantee that there are no duplicates in a list of random numbers.

Approach 1 : Ranking random integers
Suppose you want 50 non-repeating numbers between 1 and 50.
1. Use RAND() function in cell B6:B55
2  In cell D6, type =RANK(B6,B$6:B$55) and paste it down till cell D55

Download this workbook and practice what you learn.

Approach 2 : Big Daddy Formula
Specify lower limit i.e. 1 in cell J3 and upper limit i.e. 50 in cell J4.


Copy the following formula into cell F6 and paste it down :



Hit CTRL+ SHIFT + ENTER  to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

Download this workbook and practice what you learn.

How to modify
Suppose you want 100 non-repeating numbers between 5 and 105. 
1. Specify lower limit i.e. 5 in cell J3 and upper limit i.e. 105 in cell J4.
2. In F column, paste the formula down till row F105.
ListenData Logo
Spread the Word!
Share
Related Posts
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

4 Responses to "Generating Unique Random Numbers in an interval in Excel"
  1. Thank you, saved me some headache time.

    ReplyDelete
  2. I tried the second method in Excel 2003, but the iferror function is not available there. Have you got an equivalent for Excel 2003?

    ReplyDelete

Next → ← Prev
Looks like you are using an ad blocker!

To continue reading you need to turnoff adblocker and refresh the page. We rely on advertising to help fund our site. Please whitelist us if you enjoy our content.