Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Random Number Generator without Repeat Numbers

This is a discussion on Random Number Generator without Repeat Numbers within the Excel Questions forums, part of the Question Forums category; I've created a range from G2-J5, 16 cells total to generate random numbers between 1 & 54 using "=RANDBETWEEN(1,54)". The ...

  1. #1
    New Member
    Join Date
    Feb 2007
    Posts
    34

    Question 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. #2
    Board Regular
    Join Date
    Oct 2010
    Posts
    198

    Default Re: Random Number Generator without Repeat Numbers

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

    Great example here for a UserDefinedFunction

  3. #3
    New Member
    Join Date
    Feb 2007
    Posts
    34

    Default 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. #4
    Board Regular
    Join Date
    Jan 2010
    Location
    MN, USA
    Posts
    454

    Default 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
                        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:
    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.
    Last edited by ChrisOswald; Aug 5th, 2011 at 06:52 PM. Reason: Read the whole of the original post:
    Using Office 2007
    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    Signature Shamelessly Swiped From Sticky

  5. #5
    Banned user
    Join Date
    Nov 2010
    Posts
    2,075

    Default 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. #6
    New Member
    Join Date
    Feb 2007
    Posts
    34

    Smile 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. #7
    New Member
    Join Date
    Mar 2011
    Location
    Tasmania
    Posts
    44

    Default 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.
    Last edited by Just Jan; Aug 7th, 2011 at 04:37 AM.

  8. #8
    Board Regular MrVillareal's Avatar
    Join Date
    Jun 2011
    Location
    Philippines
    Posts
    496

    Default Re: Random Number Generator without Repeat Numbers

    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

  9. #9
    Board Regular MrVillareal's Avatar
    Join Date
    Jun 2011
    Location
    Philippines
    Posts
    496

    Default 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



    Here is the link:
    http://www.youtube.com/user/ExcelIsF.../0/TAofPm4LCRc

  10. #10
    New Member
    Join Date
    Dec 2011
    Posts
    1

    Default 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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com