forcing unique random numbers

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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.

<pre>
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</pre>

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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
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/viewtopic.php?mode=viewtopic&topic=3658&forum=2&start=0

HTH,
Jay
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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