Thanks:  0
Likes:  0

# Thread: forcing unique random numbers

1. 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...s/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

Also, please post whatever you have.

Thanks,
Jay

2. Have a look at rand_unique at :-

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

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

4. april 15th.

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

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

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

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

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

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

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

## User Tag List

#### Posting Permissions

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