random numbers

[abz2max]

I need to create a list of random numbers, that do not repeat each other. The range of numbers i will need will be 1 to n. n = any number>1. how can i create a macro to do this.

[eliW]

On 2002-04-17 06:35, abz2max wrote:
I need to create a list of random numbers, that do not repeat each other. The range of numbers i will need will be 1 to n. n = any number>1. how can i create a macro to do this.

abz2max,
That issue was discussed in the past for several times.
look for example at:
http://www.mrexcel.com/board/viewtop...c=4990&forum=2
Eli

[Mark O'Brien]

God, I really don't want to turn this into a huge discussion like those other two, but this has been nagging at me:

Dave H suggested the code for picking lottery numbers:

Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer)

Dim iNum As String

Dim strNum As String

Dim i As Integer



Application.Volatile

  iNum = Int((Top - Bottom + 1) * Rnd + Bottom)



   For i = 1 To Amount

       strNum = Trim(strNum & " " & iNum)

         Do Until InStr(1, strNum, iNum) = 0

           iNum = Int((Top - Bottom + 1) * Rnd + Bottom)

         Loop

   Next i

   

RandLotto= strNum



End Function

I thought about this one day and realised that there is a flaw in this technique. This technique is biased against single digit numbers. That is to say, if "49" came up as the first pick in this code, the numbers "4" and "9" could not be individually picked as a single digit number. This is due to the "instr" check that Dave uses.

Sorry if this turns into a huge discussion again, but this seemed like a good opportunity to address this without revisiting the old threads.

[Jay Petrulis]

Hi Mark,

Excellent!! Nice catch. Will return unique numbers but is biased.

Thanks for the post.

Bye,
Jay

P.S. FYI, the following post is, in my opinion, the most amazing of this entire Excel/RNG topic.

I have specified the link to Harlan Grove's post, but for those interested, please read the whole thread, including Harlan's discussion with Tushar Mehta afterward.

http://groups.google.com/groups?hl=e...att.net&rnum=5

[Mark O'Brien]

Good thread. Interesting to me since I have a vague grasp of bias and such statistical "nonsense". (I'm an engineer, statistics have to be treated carefully in my field)

The one thing that interests me in Harlan's code is that ahe doesn't use "Randomize" to initialise VB's RNG. Now, I ran into a problem with this myself. I made a little High/Low guessing game in Excel. (kind of like "Play Your Cards Right" and whatever the name of the US version was might have been "Jokers Wild") Anyway, without initialising the RNG I found that if I restarted to play my game again, I got the same "random" numbers each time.

I think using "Randomize" changes the seed. Again, I don't want to turn this into a hee-yuge discussion, but I know that it's going to head that way.

[Jay Petrulis]

Hi,

I just posted to .worksheetfunctions asking this. I should get a few responses soon, and Harlan will respond if he sees the post.

Bye,
Jay

[Jack in the UK]

why not find at random.. not produce random?

[Jay Petrulis]

Hi,

Here is Harlan's response:

Jay Petrulis wrote...
....
>I have one question, for Harlan in particular, about his
>code. Could it be improved (made "more random") if
>
>Randomize
>
>is used to initialize the RNG and was included before the
>VBA Rnd function is invoked, so that the "seed" is
>reshuffled? Was/is it unnecessary in this instance?
....

It never hurts. Just because I didn't include it doesn't mean I had a reason
not to include it other than simply forgetting to do so.

Note: all Rnd calls in all VBA procedures use the same pseudorandom number
generator, so it's only necessary to call Randomize once in any Excel
session (though I guess it might be necessary to call it in each additional
Excel application instance if using multiple Excel sessions - dunno whether
the state variables are stored in shared DLL memory space or in each
application instance's own memory space). I call Randomize in Worksheet_Open
in my Personal.xls file, so I never think of adding it elsewhere.

Didn't know that one session thing. Calling Randomize when Excel is opened is pretty cool. Adding Randomize to his code would make his routine even better, for those who haven't already initialized the RNG with it.

Bye,
Jay