Random Number Generator without Repeat Numbers

Thanks:  0

# Thread: Random Number Generator without Repeat Numbers

1. ## Random Number Generator without Repeat Numbers

I've created a range from G2-J5, 16 cells total to generate random numbers between 1 & 54 using "=RANDBETWEEN(1,54)". The problem is I'm getting duplicate numbers within the 16 cells. How can I modify the formula to eliminate duplicate numbers?

Thanks, Tim

2. ## Re: Random Number Generator without Repeat Numbers

http://www.ozgrid.com/VBA/RandomNumbers.htm

Great example here for a UserDefinedFunction

3. ## Re: Random Number Generator without Repeat Numbers

Thanks for the link. The function works but it generates 16 unique numbers in one cell. I need 16 unique numbers, one number in each cell. I've also noticed when using this function, I'm not able to reference the results with VLOOKUP to generate a text output which I could do when using "=RANDBETWEEN(1,54)".

Thanks, Tim

4. ## Re: Random Number Generator without Repeat Numbers

Hi,

Here's a udf that should be pretty good substitute for the RandBetween(). It'll return an error if a random number can't be generated. I'd be wary of trying to generate a really big data set with it.

Code:
```Public Function randBetweenExcludeRange(lngBottom As Long, lngTop As Long, _
rngExcludeValues As Range) As Variant
Dim c                               As Range
Dim dict                            As Object
Dim i                               As Long
Dim blNoItemsAvailable              As Boolean
Dim lngTest                         As Long
'some notes on code:
'It'd probably be a good idea to check for values that are only integers in the range
'you might be able to sort the already excluded values, choose a number between 1 and
'the number of remaining available values and then generate that from a full list of
'values.  (maybe by making the dictionary hold available values only?)

'I'm pretty sure the comment above doesn't make a lot of sense.  If it
'did, i'd have tried to implement it.

If lngBottom > lngTop Then
randBetweenExcludeRange = CVErr(xlErrNA)
Exit Function
End If
'get a list of all items in range
'i = 0
Set dict = CreateObject("Scripting.dictionary")
For Each c In rngExcludeValues
'I should have really only checked for c.values that are longs.
If IsNumeric(c.Value) Then
If c.Value >= lngBottom And c.Value <= lngTop Then
If Not dict.exists(c.Value) Then
End If
End If
End If
Next c

'check to make sure that there are values available to use
If dict.Count >= lngTop - lngBottom + 1 Then
'initialize error holder to true
blNoItemsAvailable = True
For i = lngBottom To lngTop
If Not dict.exists(i) Then
blNoItemsAvailable = False
Exit For
End If
Next i
End If
If blNoItemsAvailable Then
randBetweenExcludeRange = CVErr(xlErrNA)
Exit Function
End If
'this bit could (probably) be made a lot more efficient.  see notes at top
'of code

Do
lngTest = Int(Rnd() * (lngTop - lngBottom + 1)) + lngBottom
If Not dict.exists(lngTest) Then
randBetweenExcludeRange = lngTest
Exit Function
End If
Loop
End Function```
Example of use:
Sheet1
A
1Test
26
33
44
58
61
79
85
910
107
112
12#N/A
13#N/A
Excel 2007

Worksheet Formulas
CellFormula
A2=randBetweenExcludeRange(1,10,A\$1:A1)
A3=randBetweenExcludeRange(1,10,A\$1:A2)
A4=randBetweenExcludeRange(1,10,A\$1:A3)
A5=randBetweenExcludeRange(1,10,A\$1:A4)
A6=randBetweenExcludeRange(1,10,A\$1:A5)
A7=randBetweenExcludeRange(1,10,A\$1:A6)
A8=randBetweenExcludeRange(1,10,A\$1:A7)
A9=randBetweenExcludeRange(1,10,A\$1:A8)
A10=randBetweenExcludeRange(1,10,A\$1:A9)
A11=randBetweenExcludeRange(1,10,A\$1:A10)
A12=randBetweenExcludeRange(1,10,A\$1:A11)
A13=randBetweenExcludeRange(1,10,A\$1:A12)

EDIT: I just saw that you're putting these items into a square array instead of a single column or row. You'll need to mess around a little bit with index() or indirect() functions to get it to work right. Sorry, I'm heading out for the weekend now.

5. ## Re: Random Number Generator without Repeat Numbers

Hi,

Maybe this ...
Code:
```Sub generateuniquerandom()
Dim b() As Boolean, e As Range, k&, x&
ReDim b(1 To 54)
For Each e In Range("G2:J5")
Do
x = Int(Rnd() * 54) + 1
If b(x) = False Then
e.Value = x
b(x) = True
Exit Do
End If
k = k + 1: If k > 100 Then Exit Sub
Loop
Next
End Sub```
Can do it as a UDF if you like.

6. ## Re: Random Number Generator without Repeat Numbers

Chris...

Thanks so much. I was able to tweak it a little bit & it works great!

Thanks, Tim

7. ## Re: Random Number Generator without Repeat Numbers

Col A insert numbers 1 to 54
Col B cell 1 insert =RAND() copy down to B54
Col C cell 1 insert =INDEX(A:A,MATCH(SMALL(B:B,ROWS(\$1:1)),B:B,0))
Copy down to cell C16
As a check for duplication in col D cell 1 =IF(COUNTIF(C:C,C1)>1,"Duplicate","") copy down to D16

There is a large pool of numbers between 0 and 1 to practically ensure the absence of duplicate random numbers in such a small sample.

Echo the results in C1 to C16 into your selected range G2 to J5

Might want to turn off Autocalculate as well to stop the numbers from changing all the time.

8. ## Re: Random Number Generator without Repeat Numbers

Hi friend

Select G2:J5
Active cell in G2
=mrand(16,1,54)
Ctrl Shift Enter

9. ## Re: Random Number Generator without Repeat Numbers

Sheet23
GHIJ
2163398
335381219
451303111
57484050
Excel 2010

Array Formulas
CellFormula
G2:J5=MRAND(16,1,54,TRUE)
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

10. ## Re: Random Number Generator without Repeat Numbers

Try putting numbers 1 thru 54 in Column A, then enter =RAND() in B1, and drag down to B54. Then sort columns A and B by Column B, and take the first 16 numbers in Column A. They will now be 16 randomly chosen numbers between 1 and 54.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•