Random Number Generator

[dok112]

Hello everyone,

I could use some help from y'all. I have a macro that is going to loo
through the range (A1:A100), and use the number in that particular cel
to fill in range(B1:B100) with the corresponding question. Now, what
need help creating, is a random number generator to fill in th
range(A1:A100) with a random number. There are approximately 50
questions that will be available, so I don't want it to use the sam
question twice. How can I create a macro to randomly generate a numbe
from 1 to 500 and enter them into range(A1:A100) and not using the sam
number again...

Any help will be greatly appreciated

 

[Tom Ogilvy]

Sub GenRandom()
With Range("A1:A500")
.Formula = "=Row()"
.Formula = .Value
.Offset(0, 1).Formula = "=rand()"
.Resize(, 2).Sort Key1:=Range("B1"), Header:=xlNo
End With
Columns(2).ClearContents
Range("A101:A500").ClearContents
End Sub

 

[davidm]

Just for academic interest:

Sub Rand100From500()

For Each c In Range("a1:a100")
Do Until Application.CountIf(Range("a1:a100"), c.Value) = 1
c.Value = Int(Rnd * 500 + 1)
Loop
Next

End Sub



David

 

[Tom Ogilvy]

for further academic interest, your method is faster until about 160 numbers
are generated, then my method is faster. At least in my tests.

Obviously the requirement here was for only 100.

 

[davidm]

Tom,

For all practical purposes, using random numbers to sort does the jo
but still on the object of academic interest, what happens if th
random numbers generated are *not* 100% unique?

Put another way, if it is -conceivable - that two random number
returned by the RND function could be exactly the same, we can d
violence to the facts and logically assume that in a very very rar
moment of aberration, all the random numbers returned in a range wil
be equal. That being the case, the sorting mechanimsm will b
stullified.

I do concede that the probability of getting 2 random numbers the sam
in a range of 65536 rows should be infinistemally close to zero, an
the Prob(E) of having all 65536 instances even more so. However, fo
as long as Prob(E) <>0, we cannot guarantee *100% accuracy* in sortin
-in all cases -and -at all times-. In the context of pure mathematica
accuracy, Randomization could yet fail us.

Food for thought.


Davi

 

[Bill Martin]

Tom,

For all practical purposes, using random numbers to sort does the job
but still on the object of academic interest, what happens if the
random numbers generated are *not* 100% unique?

Put another way, if it is -conceivable - that two random numbers
returned by the RND function could be exactly the same, we can do
violence to the facts and logically assume that in a very very rare
moment of aberration, all the random numbers returned in a range will
be equal. That being the case, the sorting mechanimsm will be
stullified.

I do concede that the probability of getting 2 random numbers the same
in a range of 65536 rows should be infinistemally close to zero, and
the Prob(E) of having all 65536 instances even more so. However, for
as long as Prob(E) <>0, we cannot guarantee *100% accuracy* in sorting
-in all cases -and -at all times-. In the context of pure mathematical
accuracy, Randomization could yet fail us.

Food for thought.


David

-----------------

Why does it matter? You do the sort on the "random" numbers and it still puts
the other column into some unique order without repeats.

Bill

 

[davidm]

Bill wrote:

< You do the sort on the "random" numbers and it still puts
<the other column into *some unique order *without repeats.


You miss the point, Bill. In the weirdest extreme when all "random
numbers are equal, sorting for, say, 100 numbers from 1-500 (as in th
original request) will merely return the serial numbers 1-100. Whic
technically is a sample but arguably not a desirable one. Representin
a varying ascending degree of imperfection, notice that there are myria
scenarios between (1) the case of having only 2 repeats in a range t
(2) the case of having of all "random" values in a range equal.

The whole issue is of course purely academic and in all likelihood, i
you were to generate 65536 random numbers a million times, there is
good chance that you would end up with 65536 * 1 million unique sets o
random numbers!

David

 

[Bill Martin]

Bill wrote:

< You do the sort on the "random" numbers and it still puts
<the other column into *some unique order *without repeats.


You miss the point, Bill. In the weirdest extreme when all "random"
numbers are equal, sorting for, say, 100 numbers from 1-500 (as in the
original request) will merely return the serial numbers 1-100. Which
technically is a sample but arguably not a desirable one. Representing
a varying ascending degree of imperfection, notice that there are myriad
scenarios between (1) the case of having only 2 repeats in a range to
(2) the case of having of all "random" values in a range equal.

The whole issue is of course purely academic and in all likelihood, if
you were to generate 65536 random numbers a million times, there is a
good chance that you would end up with 65536 * 1 million unique sets of
random numbers!

David.

-----------------

Actually, I think you missed the point...

If you accept your initial premise that Excel has randomly picked all 100
numbers at identical values (one in a zillion chance), then it is equally true
that the end result of having all the sorted numbers in consecutive order is
also a valid random result (one in a zillion chance).

Note that the task is to set a random order, not a "desirable" one.

Bill