This article shows how to generate unique random numbers in an interval in Excel.

Microsoft Excel includes a built-in function called **RANDBETWEEN** that generates a random number within a specified range. However, the numbers generated by this function can sometimes repeat. In this article, we will cover two different methods to ensure that a list of random numbers does not contain any duplicates.

Method 1 : Rank random integers

Follow the steps below to generate non-repeating random numbers between 1 and 50.

- Enter the
**RAND()**function in cells B6 to B55. - In cell D6, input the formula
**=RANK(B6,B$6:B$55)**, and drag it down to cell D55.

Method 2 : Array 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 :

=IFERROR(LARGE(ROW(INDIRECT($J$3&":"&$J$4))*NOT(COUNTIF($F$5:F5,ROW(INDIRECT($J$3&":"&$J$4)))),RANDBETWEEN(1,$J$4-$J$3-ROW(A1)+2)),"")Hit

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

Download the working workbook.

How does the formula work?

- The formula uses the
**INDIRECT**function along with cell references J3 and J4 to create a dynamic range. - The
**ROW**function generates an array of numbers corresponding to the range created in step 1. - The
**COUNTIF**function checks if each number in the array already exists in the cells above in column F. - The
**NOT**function inverts the results of the COUNTIF function. - The product of the array and the inverted results is passed to the LARGE function.
- The
**LARGE**function returns the kth largest value from the array, where k is the position of the number in the sorted array. - If an error occurs, the formula falls back to the RANDBETWEEN function to generate a random number.

How to modify the formula

Suppose you want 100 unique numbers between 5 and 105.

- Enter the lower limit, 5, in cell J3 and the upper limit, 105, in cell J4.
- In column F, copy the formula down to row F105.

thank you so much

ReplyDeleteThank you, saved me some headache time.

ReplyDeletethanks

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

ReplyDelete