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.

<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

 

[Jay Petrulis]

Hi Jack,

I think we are discussing two different things. I believe you are trying to get a random number generator which is truly random, while I am reporting some procedures which will result in no duplicate values.

Dave's code resamples if a duplicate is found, so while not technically uniquely random, it overcomes it nicely.

I've read that the period for the VBA RND() function is 2^24, so for most work it should be OK. When you truncate or round to integers, you will encounter some duplicates.

The Microsoft KB article:
http://support.microsoft.com/support/kb/articles/Q86/5/23.ASP

shows the RAND() algorithm to produce about 1 million numbers.

Using Randomize before the RND() in VBA should enable you to get "randomness" to a high degree of statistical precision.

Another random selector from Laurent Longre is shown. This one reads the values into an array, so it would be good within the VBA environment.

---------------------------
Option Base 1
Dim Res() As Integer
Dim I As Integer
Dim J As Integer
Dim Transit As Integer
Const NBVALS = 15

ReDim Res(NBVALS)
For I = 1 To NBVALS
Res(I) = I
Next I
For I = 1 To NBVALS - 1
J = Int(Rnd() * (NBVALS - I + 1)) + I
Transit = Res(I)
Res(I) = Res(J)
Res(J) = Transit
Next I
-----------------------------

If you are looking for normal variates,
=norminv(rand(),mean,stdev)
should be OK for most work.

If you want to see what I believe is the pinnacle of correcting Excel's problems with this, see Jerry Lewis's code for the PNorm at

http://groups.google.com/groups?selm=3BB620CB.40404@no_e-mail.com

Also, please post whatever you have.

Thanks,
Jay

 

[toofoo]

Have a look at rand_unique at :-

http://www.xl-logic.com/pages/formulas.html

 

[Jack in the UK]

Is that not waht you asked in the subjust .. unique random numbers..


exceptable randon is OK yes there are qways as shown VBA will easy do this but soory if i git it wrong mut i read UNIQUE as NEVER a repeat.. right or wrong thats my take, thus the comments.

at all wahts exceptable containg i see as not random, randon can never repeat ..

buts its you decisions in not disagreeing but im not agreeing, such is the fun of this board we all learn things, i have learnt exceptable can be OK and no need to progress further to buzlight year enfinity and beyond, which was my call, just a view,

I have VBA in lotto numbers which ill post ASAP it works on FIND not rand which again is my thinking to random, why do i have to produce randon numbers = can i not select them at random..... same result but floorless.

I would love to post my scripts but i dont even have excel loaded just XP Windows and IE so sadly i can, not even my email, im testing XP horrid thing.. pester me ill post ASAP.

If you win, treat some guys less off than us, thats cool dont you think?

 

[Mark O'Brien]

april 15th.

 

[Jack in the UK]

ERrmm.. looks like that is solved and the matter of non repeats is addressed, not being difficult, but i must say Dave has hit on something, Excel if programmed can be forced to detect duos and recal.. thus ebd result correct... Mark you seem to have over come this with nice code, i cant test as no Excel, still texting Win XP, youck!

but i will and ill post what i find, very intresting and many will have learnt, i guess not back to fomula to do this also, and again must be random, and not predicatable.

random is as BuzLight Years says infinaty and beyond, but contain it ie 1-49 req 6 intergers??? randon between this must be as you have displayed.. food for thought, we have formula that will give 1 billion to 1 chance or repeat but thats not exceptable, needs to be NONE in each run...

good stuff

 

[Jay Petrulis]

Hi Jack and Mark,

When you are talking about Dave Hawley's RandLotto function returning duplicate values, are you saying

1. That there is a chance that the same set of six numbers will appear more than once if the simulation is run x number of times, where x is too low a number to be more than coincidental?

2. Or, are you saying that an individual draw of six could produce a duplicate?

If it is 2 above, doesn't the loop preclude that, where he resamples until the matches are zero?

I don't understand how these duplication issues occur in these functions/procedures.

In the various threads, we have:

1. RandLotto function
2. HGSample function
3. HGSample2a function
4. Tom Ogilvy's sub
5. Mark's Main/RandOrder function
6. RandUnique link from the prior post in this thread

There are six different options, but you are guaranteeing only one of them. Why not the other five? I don't understand where the duplicates arise. Please advise.

BTW, Mark, excellent job on the routine. Very nice post.

Bye,
Jay

 

[Mark O'Brien]

Apologies and thanks for making me look at Dave's code again Jay. I was expecting an "If" statement, but on my first glance I didn't notice his rather snifty use if "Instr".

I declare Dave the winner. Unless my code is faster. (Which I doubt) I really just wanted to say that old trick of using an array anyway.

 

[Jay Petrulis]

Hi Mark,

No apologies needed. These were terrific efforts by you and Dave and the others. These were great threads, too, with great questions and discussions.

*All* of these are in my keeper file.

Bye,
Jay

 

[Yogi Anand]

It has been a pretty informative discussion. Practically speaking, I need to generate say 50 questions from a bank of several hundred questions for a test. I do not want any two tests to be alike. So, I want to have the 50 questions selected and ordered randomly. I go through several steps to accomplish this.
First I select more than 50 questions using ...
TOOLS|DATA_Analysis|Sampling ... then Input Range, and the number of samples -- there are repeats in this list
Then I filter this list to select 50 unique numbers

This may not be the most efficient way, nor it may be truly random, but I just wanted to share with you my approach.

_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line
This message was edited by Yogi Anand on 2003-01-19 18:15

 

[Adcb]

Here's another way of doing it, without using a loop. This should be quicker (but not checked for this).
It is based on the method used (Rand_Unique) at http://www.xl-logic.com/pages/formulas.html as suggested by "toofoo".
A macro could be created with the macro recorder (no VBA knowledge required).

Sub RandomOrder()
Application.ScreenUpdating = False
With [B1]
.Value = 1
.AutoFill Destination:=[B1:B233], Type:=xlFillSeries
End With
[A1:A233].Formula = "=RAND()"
Columns("A:B").Sort Key1:=[A1]
Columns(1).Delete
End Sub