Random Number Generator without Repeat Numbers

tronchetti

New Member
Joined
Feb 13, 2007
Messages
37
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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
                    dict.Add c.Value, ""
                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:
Excel Workbook
A
1Test
26
33
44
58
61
79
85
910
107
112
12#N/A
13#N/A
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
Hi friend:)

You can download "morefunc" and use "mrand" function.

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

Hope your feedback:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top