Try Random Number Generator

Hello everyone,

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

Any help will be greatly appreciated.

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



--
Regards,
Tom Ogilvy




"dok112" <[email protected]> wrote in
message news:[email protected]...
>
> Hello everyone,
>
> I could use some help from y'all. I have a macro that is going to loop
> through the range (A1:A100), and use the number in that particular cell
> to fill in range(B1:B100) with the corresponding question. Now, what I
> need help creating, is a random number generator to fill in the
> range(A1:A100) with a random number. There are approximately 500
> questions that will be available, so I don't want it to use the same
> question twice. How can I create a macro to randomly generate a number
> from 1 to 500 and enter them into range(A1:A100) and not using the same
> number again...
>
> Any help will be greatly appreciated.
>
>
> --
> dok112
> ------------------------------------------------------------------------
> dok112's Profile:
http://www.excelforum.com/member.php...o&userid=10581
> View this thread: http://www.excelforum.com/showthread...hreadid=484726
>

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

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.

--
Regards,
Tom Ogilvy


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=484726
>

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

davidm wrote:
> 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

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.

davidm wrote:
> 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