Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Unique Random numbers (2003)

    Loungers,

    I have a list of 30 names that I want to allocate unique random numbers to between 1 - 30 - ie no duplicate numbers.

    I've tried =Randbetween() but this produces duplicate numbers

    Any thoughts?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Random numbers (2003)

    Dean

    This should do the job

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Random numbers (2003)

    Enter the numbers 1 ... 30 in a column next to the names (for example using Edit | Fill Down | Series...).
    In the cells to the right of that, enter the formula =RAND().
    Select these two columns of cells (not the column with the names!)
    Sort on the column with random numbers. This will shuffle the numbers 1 ... 30 in a random order.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Unique Random numbers (2003)

    Jerry,

    Thanks for the quick reply - that works fine, however I need to allocate a unique number in 23 cells to be able to generate a vlookup to a list of names.

    Any other thoughts?

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Unique Random numbers (2003)

    Hans - Thanks for the reply - that works fine

    Thanks again

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Random numbers (2003)

    Dean

    Here is a VBA response

    Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
    ' creates an array with NumCount unique long random numbers in the range LLimit - ULimit (including)
    Dim RandColl As Collection, i As Long, varTemp() As Long
    UniqueRandomNumbers = False
    If NumCount < 1 Then Exit Function
    If LLimit > ULimit Then Exit Function
    If NumCount > (ULimit - LLimit + 1) Then Exit Function
    Set RandColl = New Collection
    Randomize
    Do
    On Error Resume Next
    i = CLng(Rnd * (ULimit - LLimit) + LLimit)
    RandColl.Add i, CStr(i)
    On Error GoTo 0
    Loop Until RandColl.Count = NumCount
    ReDim varTemp(1 To NumCount)
    For i = 1 To NumCount
    varTemp(i) = RandColl(i)
    Next i
    Set RandColl = Nothing
    UniqueRandomNumbers = varTemp
    Erase varTemp
    End Function


    Sub TestUniqueRandomNumbers()
    Dim varrRandomNumberList As Variant
    varrRandomNumberList = UniqueRandomNumbers(30, 1, 100)
    Range(Cells(1, 1), Cells(30, 1)).Value = _
    Application.Transpose(varrRandomNumberList)
    End Sub
    Jerry

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Unique Random numbers (2003)

    Jerry - will give that a shot also

    Thanks very much for your assistance

Posting Permissions

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