forcing unique random numbers

[Mark O'Brien]

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

[clevow]

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

[Jay Petrulis]

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

[clevow]

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!

[Jack in the UK]

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

[Jay Petrulis]

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

[Jay Petrulis]

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.

[Jack in the UK]

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

[Chris Davison]

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

[Chris Davison]

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)