MS Excel has an inbuilt function i.e.

Suppose you want 50 non-repeating numbers between 1 and 50.

1. Use

2 In cell D6, type

Download this workbook and practice what you learn.

Specify lower limit i.e. 1 in cell J3 and upper limit i.e. 50 in cell J4.

Hit

Download this workbook and practice what you learn.

**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:B552 In cell D6, type

**=RANK(B6,B$6:B$55)**and paste it down till cell D55Download 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.

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