Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: forcing unique random numbers

  1. #1
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, to go back to the original post. Here is a bit of VBA that will give a random order for the range 1 to 233. Each number is used only once. I 100% guarantee that there can be no duplicates because I used an old ZX Spectrum card shuffling technique of using an array to hold which numbers have and have not been used. It took about 1 second for the complete code to run and put data in column A. This is a AMD 233 MHz machine I'm using.


    Public Sub main()
    Dim iRandArray() As Integer
    Dim i As Integer
    RandomOrder 233, iRandArray()

    For i = 1 To UBound(iRandArray)
    Sheets("Sheet1").Range("A1").Offset(i, 0).Value = iRandArray(i)
    Next

    End Sub
    Private Sub RandomOrder(ByVal iNumRange As Integer, ByRef iRandArray() As Integer)

    Dim iNumArray() As Integer
    Dim iRandValue As Integer

    ReDim iNumArray(1 To iNumRange, 1 To 2)
    ReDim iRandArray(1 To iNumRange)

    For i = 1 To iNumRange
    iNumArray(i, 1) = i
    iNumArray(i, 2) = 1
    Next


    For i = 1 To iNumRange
    Randomize
    iRandValue = Int((iNumRange * Rnd) + 1)

    If iNumArray(iRandValue, 2) = 0 Then
    i = i - 1
    Else
    iRandArray(i) = iRandValue
    iNumArray(iRandValue, 2) = 0
    End If
    Next

    End Sub


    I think that I agree with Jack about Dave's code possibly giving repeats, but he it seems that he reduces the likelyhood of this with the way he's coded it.

    HTH

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone know of a way to force a list of random numbers with no two numbers the same? I need a list 1-233 with each number being used exactly once.

    Any help would be appreciated. Thanks!

    -Carrie

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 10:23, clevow wrote:
    Does anyone know of a way to force a list of random numbers with no two numbers the same? I need a list 1-233 with each number being used exactly once.

    Any help would be appreciated. Thanks!

    -Carrie
    Hi Carrie,

    At the end of the following post (page 3), both Dave Hawley and I posted code to do exactly what you want.

    You'll have to change the code to suit your needs (233 in samplem 233 selected), but you won't get duplicates.

    Found at this link:
    http://www.mrexcel.com/board/viewtop...orum=2&start=0

    HTH,
    Jay

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was hoping for a formula, but I guess that's not possible, judging by the thread you referred me to!

    I think this'll work. Thanks!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay

    These posts clearly satae this rand function FAILS, not 100%, i have VBA thats truly 100% lottery no choose, Chris D i sent it to and it NEVER fails,,,,,, this can easy be converted to 223 and select as many diff nos as reqd.

    OK can this be one cell one formula if nested return the 6 numbers of 233 numbers...

    YES i truly beleive the girls i love so much "excel" can and wil do this its a case of how... so far we are close SOON we will post if hope and get the reqd answer RAND numbers to any situation,,, ALL One cell that means looking at its self...

    let me know ill post code.. and amend... formual not so easy


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 10:59, Jack in the UK wrote:
    Hi Jay

    These posts clearly satae this rand function FAILS, not 100%, i have VBA thats truly 100% lottery no choose, Chris D i sent it to and it NEVER fails,,,,,, this can easy be converted to 223 and select as many diff nos as reqd.

    OK can this be one cell one formula if nested return the 6 numbers of 233 numbers...

    YES i truly beleive the girls i love so much "excel" can and wil do this its a case of how... so far we are close SOON we will post if hope and get the reqd answer RAND numbers to any situation,,, ALL One cell that means looking at its self...

    let me know ill post code.. and amend... formual not so easy


    Hi Jack,

    Tom's code will not repeat. Neither does Dave's UDF. Both of these overcome the problems the OP from that thread was having.

    Please post your code, so that we can see another way. But, I think the OP in this thread will have success with either of the two referenced posts.

    Thanks,
    Jay


  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 10:57, clevow wrote:
    I was hoping for a formula, but I guess that's not possible, judging by the thread you referred me to!

    I think this'll work. Thanks!
    Dave Hawley's RandLotto is a function you can call from a worksheet cell.

    I am unsure what you need. If you need a sequential list in random order, Tom Ogilvy's code will work, too.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay

    Dave Hawley is a very close friend as far ways as weare in the world and i would agree withDave he say white and i dsay black ill still agree,

    this code will fail, Daves notr convinced...

    Aladins a great pal also and again a will agree and except his call regardless, these guys are never less than perfect, but the post will fail..

    the feed do not say they will be 100% randon.

    I beleive Rand is not random, and thisis as i have proved to Chris D yesterday i can predict the future rand answer OK will take time but thats not random as i see it, im not saying the answers not OK im am saying RANDON ie rand is really random..

    Chris will but in,, and tell his side

    the problem in RAND as we use it and as these posts do is its contained, so is workable and predictable,....

    Rand 1-10 i can guess ok i only guess correct 2 in ten is that rand... but guess rand in enfinity as Buz Ligtyear shouts and go beyond then thats random... and control is not random.... bit random between two goal posts.

    Chris and i are working TRUE randon...

    fut find in selection id random as hjow can you apply maths to find.. you copuld say porberbility but that not the same question as rand..

    is random randon if controlled.. i say not..

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the poster in this thread just wants a formula, which is entirely achievable using more than one cell or UDFs or named rnages and named formulae, so no big dramas there.

    The challenge in the Easter posting was either a) a single formula with no precedents, just a totally self-encapsulated formula or b) a single line of VBA

    (no referencing cells, no named formulae nothing).... this is still open and unresolved)

    in my opinion also, it should also stand up to mathematical scrutiny in terms of true randomness and the probablitiy of duplicates - but I knwo this board has a very deep wealth of people who can comment on this if the formula ever surfaces

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 10:23, clevow wrote:
    Does anyone know of a way to force a list of random numbers with no two numbers the same? I need a list 1-233 with each number being used exactly once.

    Any help would be appreciated. Thanks!

    -Carrie
    in cell A1 try : =RAND()*100,000,000,000

    and copy down to cell A233

    in cell B1 try : =RANK(A1,$A$1:$A$233,1)

    and copy down to cell B233

    in column B you now have a ranodmly-ordered list of the numbers from 1 to 233 with the probablitiy of a repeat of around a hundred thousand million to one (Celtic winning the double next year, again)


    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

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
  •