Rand-Randbetween
ABC Computer
duplication in 180 columns or rows.
Is this possible to do in excel? How?
Thank you very much
FB
Norman Harker
If you have it, John Wakenbach's "Excel 2000 Formulas" has code for this on
pages 524-6
You'd need to do a bit of editing to generate random numbers between 1 and
180
hth
"ABC Computer" <fr...@xcomputerabc.com> wrote in message
news:WKS37.11730$ca.30...@typhoon.we.rr.com...
J.E. McGimpsey
macro:
Dim myRands(1 To 180) As Integer
Dim Pool(1 To 180) As Integer
Dim randCheck As Integer
Dim i As Integer
Dim foundRand As Boolean
For i = 1 To 180
Pool(i) = i
Next i
For i = 1 To 180
foundRand = False
Do While foundRand = False
randCheck = Rnd() * 180
If randCheck > 0 Then
If Pool(randCheck) > 0 Then
foundRand = True
myRands(i) = Pool(randCheck)
Pool(randCheck) = 0
End If
End If
Loop
Next i
'180 rows
Range("A1:A180").Value = Application.Transpose(myRands)
'or 180 columns
'Range("A1:FX1").Value = myRands
End Sub
In article <WKS37.11730$ca.30...@typhoon.we.rr.com>,
"ABC Computer" <fr...@xcomputerabc.com> wrote:
--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail
J.E. McGimpsey
Public Sub RandNoDupes()
at the top of the macro.
Probably also want to put the method
Randomize
before the first For i = 1 to 180
In article <arjf-B0A4B3.0...@msnews.microsoft.com>,
David McRitchie
A simpler and probably faster solution, particularly as
you increase the scope of the numbers, where each
number must be represented..
Generate 1 to 180 down a column, and place =RAND()
in the next empty column. Convert the random numbers
to constants: copy (ctrl+c), edit, paste special, values
and then sort on the random numbers
column, finally eliminate the random numbers column.
Record a macro doing the above if you want to do this often.
I expect this would be more efficient than
generating a random number between a range and then checking it
to make sure that it doesn't duplicate a previous number.
Option Explicit
Sub Randomized1to180()
'create numbers 1 to 180 in column a, randomize the order
Range("a1").Formula = "1"
Range("a2").Formula = "2"
Range("A1:A2").AutoFill Range("A1:A180")
Range("b1").Formula = "=RAND()"
Range("b1").AutoFill Range("B1:B180")
Range("B1:B180").Select
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("1:180").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("b:b").Delete
Range("a1").Select
End Sub
FWIW, Under 1 second on my machine,
Upping from 180 to 64000 runs about 10 seconds.
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Norman Harker" <nha...@ozemail.com.au> wrote in message news:xwT37.91609$Rr4.2...@ozemail.com.au...
> Hi Frank!
>
> If you have it, John Wakenbach's "Excel 2000 Formulas" has code for this on
> pages 524-6
>
> You'd need to do a bit of editing to generate random numbers between 1 and
> 180
>
> "ABC Computer" <fr...@xcomputerabc.com> wrote in message
David J. Braden
won't get any duplicates.
Otherwise, use what John suggested or, faster, use
Function GenA2(ByVal n As Long, ByVal lo As Long, ByVal hi As Long)
'Written 1999/7/7 David J. Braden
'PLEASE retain all comments: Posted to microsoft.public.excel.*
Dim i As Long, j As Long
lo = lo - 1
hi = hi - lo
If n > hi Then
GenA2 = CVErr(xlErrNum)
Exit Function
End If
ReDim temp(1 To hi) As Long
For i = 1 To hi
temp(i) = i
Next
hi = hi + 1
ReDim ret(1 To n, 1 To 1) As Long
For i = 1 To n
j = i + Int(Rnd * (hi - i))
ret(i, 1) = temp(j) + lo: temp(j) = temp(i)
Next i
GenA2 = ret
End Function
HTH
Dave Braden
MVP = Excel
In article <WKS37.11730$ca.30...@typhoon.we.rr.com>, ABC Computer
<fr...@xcomputerabc.com> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
J.E. McGimpsey
Mac - for which we know that MS hasn't optimized its math libraries). I
expected it to be much faster than the slightly revised routine I posted
earlier, but I found that for 64K numbers, I got an average (50 trials)
of 5.02 seconds using
Public Sub RandNoDupes(N As Long)
Dim myRands() As Long
Dim Pool() As Long
Dim randCheck As Long
Dim i As Long
Dim foundRand As Boolean
ReDim myRands(1 To N, 1 To 1)
ReDim Pool(1 To N)
Randomize
For i = 1 To N
Pool(i) = i
Next i
For i = 1 To N
foundRand = False
Do While foundRand = False
randCheck = Rnd() * N
If randCheck > 0 Then
If Pool(randCheck) > 0 Then
foundRand = True
myRands(i, 1) = Pool(randCheck)
Pool(randCheck) = 0
End If
End If
Loop
Next i
Range("A1:A" & N).Value = myRands
End Sub
My intuition was that increased collisions would have dramatically
slowed this routine as N increased, but it appears to slow not much more
than linearly.
If you have the time, I'd be interested in what you get for timing on
your machine.
In article <uLjuJ9GDBHA.1260@tkmsftngp04>,
"David McRitchie" <dmcri...@msn.com> wrote:
--
David J. Braden
Strange. I posted 4 routines a while back on this, comparing them, also
on a Mac, with nothing like file-sharing in the background, using xl98,
under OS 8.6, Rev.1 G3 with no RAM disk.
I'll run it again under 9.1 and see what the story is. The 4 routines
were posted in .programming in response to Tom and Dana; we see this
stuff so often that I figured I's see what's best when.
When you say you did this for 64000 numbers, I assume you mean you
wanted 1 to 64000 randomly shuffled? Or do yo mean you just wanted 180
out of 64000 numbers, in which case other routines are faster.
Dave Braden
In article <arjf-0E6E2A.1...@msnews.microsoft.com>, J.E.
McGimpsey <ar...@zptvzcfrl.pbz> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
David J. Braden
got it! I read your loop too quickly, saw the do-loop and imputed
another algorithm; nope, for *integers* (or things like single
characters that can be papped to such) yours is a great way to go (as
long as memory isn't an issue); I had the more general problem in mind
where the shuffle is on, say, arbitrary data.
Sorry for the false alarm.
Dave
ps - my very first idea is still the fastest, by far, subject to the
stated, not assumed, constraints <g>
In article <arjf-0E6E2A.1...@msnews.microsoft.com>, J.E.
McGimpsey <ar...@zptvzcfrl.pbz> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
J.E. McGimpsey
In article <140720011351027737%no...@ugotta.bekidding.com>,
"David J. Braden" <no...@ugotta.bekidding.com> wrote:
> When you say you did this for 64000 numbers, I assume you mean you
> wanted 1 to 64000 randomly shuffled? Or do yo mean you just wanted 180
> out of 64000 numbers, in which case other routines are faster.
--
David McRitchie
I guess you've proven that arrays are faster -- no matter what.
I was expecting a sort to be the fastest way rearranging .
Should have realized if Leo Heuser wrote a faster sort to use
with arrays rather than using Excel worksheet that the sort
in Excel must be slow.
sort on the worksheet couldn't be wort
But even if I removed the sort 2.845 secs is
longer than results for yours. As a matter of fact stripping
out all of the randomize populating only
the original values in column a takes 1.073 to 1.090 sec which
does nothing, yet it still takes more time.
0.965 sec 0.998 sec arrays screen refresh vs no screen refresh
8.051 sec 7.937 sec sort screen refresh vs no screen refresh
In addition yours is generic in that you can simply invoke from another
macro and change the number of rows.
Sorting for arrays can be found in (Leo Heuser, Myrna Larson, David Braden)
http://groups.google.com/groups?oi=djq&ic=1&selm=an_464836683
Don't know what effect that would have. But under 1 second looks
good to me.
And now as I go to post this I see all the other activity including from
Dave Braden.
HTH, (mine were single trials except for the one where I stripped
out more code to see where time was used and it took .02 secs longer).
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote ...
Tushar Mehta
First, there is a subtle bug (I think) in the algorithm. In assigning
Rnd()*N to a Long, XL does an implicit Round(). Hence, the probability
of picking zero or N is 1/2 the probability of picking any other number.
So, even though zero is discarded, N remains a factor.
Second, a solution w/o collisions is dramatically faster when N grows
large.
RandNoDupes KnuthNoDupes
10,000 0.1094 0.0
64,000 0.9922 0.1094
100,000 1.480 0.1602
1,000,000 13.73 1.859
5,000,000 28.29 9.719
10,000,000 50.86 19.45
KnuthNoDupes is based on something I read long, long ago. I believe it
was by Knuth. If not, it was by one of those other giants of the '70s.
Third, something very strange is going on. The performance of
RandNoDupes cannot improve as the numbers to sort increases. Yet, on a
per-number basis, the performance is improving! I added diagnostic code
to track how manu random numbers the sub must generate to do randomize
the list and the results are:
Time #random numbers
Last Iteration Total
10,000 0.1641 5,389 88,742
100,000 1.7578 71,069 1,173,967
500,000 12.31 1,236,523 7,442,627
1,000,000 16.32 904,128 10,030,263
5,000,000 30.93 59,427 16,692,199
10,000,000 43.45 1 16,777,207
38.77 4 16,777,214
38.01 12 16,777,215
43.89 3 16,777,211
The column 'Last Iter' lists the number of random numbers the program
had to generate in the very last iteration before it found the one
remaining unused entry. I would expect that, on average, the column
to equal N. The Total column lists the total number of random numbers
generated in total. Does anyone have any ideas what's going on?
The subroutines I used are below. The first RandNoDupes contains no
diagnostic code, while the second contains the code to track the total
number of random numbers.
Public Sub RandNoDupes(N As Long)
' =========== w/o any diagnostics
Dim myRands() As Long, Pool() As Long, randCheck As Long, _
i As Long, foundRand As Boolean, _
StartTime As Single, EndTime As Single
ReDim myRands(1 To N)
ReDim Pool(1 To N)
For i = 1 To N
Pool(i) = i
Next i
StartTime = Timer
Randomize
For i = 1 To N
foundRand = False
Do
randCheck = 1 + Fix(Rnd() * N)
If Pool(randCheck) > 0 Then
foundRand = True
myRands(i) = Pool(randCheck)
Pool(randCheck) = 0
End If
Loop Until foundRand
Next i
'Range("A1:A" & N).Value = myRands
EndTime = Timer
MsgBox "Sorting " & N _
& " " & (EndTime - StartTime) & " seconds"
End Sub
Public Sub KnuthNoDupes(N As Long)
' ============
Dim myRands() As Long, temp As Long, randCheck As Long, _
i As Long, StartTime As Single, _
EndTime As Single
ReDim myRands(1 To N)
For i = 1 To N
myRands(i) = i
Next i
StartTime = Timer
Randomize
For i = N To 1 Step -1
randCheck = 1 + Fix(Rnd() * i)
temp = myRands(randCheck)
myRands(randCheck) = myRands(i)
myRands(i) = temp
Next i
'Range("A1:A" & N).Value = myRands
EndTime = Timer
MsgBox "Sorting " & N _
& " took " & (EndTime - StartTime) & " seconds"
End Sub
Public Sub RandNoDupes(N As Long)
' =========== with diagostic code
Dim myRands() As Long, Pool() As Long, randCheck As Long, _
i As Long, foundRand As Boolean, _
StartTime As Single, EndTime As Single, _
NbrRandLastIter As Long, NbrRandTotal As Long
ReDim myRands(1 To N)
ReDim Pool(1 To N)
For i = 1 To N
Pool(i) = i
Next i
StartTime = Timer
Randomize
For i = 1 To N
foundRand = False
NbrRandLastIter = 0
Do
randCheck = 1 + Fix(Rnd() * N)
NbrRandLastIter = NbrRandLastIter + 1
If Pool(randCheck) > 0 Then
foundRand = True
myRands(i) = Pool(randCheck)
Pool(randCheck) = 0
End If
Loop Until foundRand
NbrRandTotal = NbrRandTotal + NbrRandLastIter
Next i
'Range("A1:A" & N).Value = myRands
EndTime = Timer
MsgBox "Sorting " & N _
& " " & (EndTime - StartTime) & " seconds" & vbCrLf _
& " " & NbrRandTotal & " - total random numbers" & vbCrLf _
& " " & NbrRandLastIter & " - random numbers in last
iteration"
End Sub
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <arjf-0E6E2A.1...@msnews.microsoft.com>, J.E. McGimpsey
<ar...@zptvzcfrl.pbz> wrote
J.E. McGimpsey
was a concurrent problem with N. Thanks for the correction.
I agree with your assessment that something strange is going on - that's
why I thought that collisions might make even the worksheet sort be
faster for 64K numbers, though I was wrong. But when I saw the times for
2000, 10K and 64K increase linearly, I thought something else was wrong,
which is why I asked for confirmation.
Without knowing anything about Rnd()'s algorithm (and being fairly sure
I couldn't really analyze it), is it possible that Rnd() is designed to
"fill holes" as each series progresses? I'm probably talking out
my...er...hat, but if Rnd() returns results that on average tend toward
the midpoint of larger gaps, that the behavior you demonstrate would
naturally fall out, especially as the numbers increase, wouldn't it? Of
course, I don't know if it's even possible to generate numbers this way.
I would also think that this would be sub-optimal design, but I've never
tried to design a random number generator, either.
In article <MPG.15baa5e41...@msnews.microsoft.com>,
Tushar Mehta <ng_p...@bigfoot.com> wrote:
--
David J. Braden
KnuthNoDupes is the same thing I posted (look). What did you guys
settle on? I'm totally thrown by what John raised, because what I
posted is faster than three other algorithms I tried for large arrays,
and by a long shot. Mine/Knuth's increases linearly with N, John's
quadratically.
TIA
Dave Braden
In article <arjf-A5D127.0...@msnews.microsoft.com>, J.E.
McGimpsey <ar...@zptvzcfrl.pbz> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
David J. Braden
the "To," "Cc," and "Newsgroups" headers for details. ]]
Tushar, I can't parse your data; columns screwed up. Could you e-mail
them to me? I think I know the answer to your query, need to make sure.
TIA
Dave BRaden
In article <MPG.15baa5e41...@msnews.microsoft.com>, Tushar
Mehta <ng_p...@bigfoot.com> wrote:
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
Norman Harker
Don't go offline on this one. I'm waiting to see if you get a solution.
I mean, its important not just because I pick my Lotto numbers using this!
Thanks. It's really fascinating to watch.
"David J. Braden" <no...@ugotta.bekidding.com> wrote in message
news:150720011309526460%no...@ugotta.bekidding.com...
Tushar Mehta
Sure would have saved me some time, but then, I probably would not have
done the timing tests ;-)
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <150720011301095079%no...@ugotta.bekidding.com>, David J. Braden
<no...@ugotta.bekidding.com> wrote
> John, Tushar,
> KnuthNoDupes is the same thing I posted (look). What did you guys
> settle on? I'm totally thrown by what John raised, because what I
> posted is faster than three other algorithms I tried for large arrays,
> and by a long shot. Mine/Knuth's increases linearly with N, John's
> quadratically.
> TIA
> Dave Braden
>
[snip]
Tushar Mehta
Timing results
RandNoDupes KnuthNoDupes
10,000 0.1094 0.0
64,000 0.9922 0.1094
100,000 1.480 0.1602
1,000,000 13.73 1.859
5,000,000 28.29 9.719
10,000,000 50.86 19.45
and results for just RandNoDupes
Time #random numbers
Last Iteration Total
10,000 0.1641 5,389 88,742
100,000 1.7578 71,069 1,173,967
500,000 12.31 1,236,523 7,442,627
1,000,000 16.32 904,128 10,030,263
5,000,000 30.93 59,427 16,692,199
10,000,000 43.45 1 16,777,207
38.77 4 16,777,214
38.01 12 16,777,215
43.89 3 16,777,211
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <150720011309526460%no...@ugotta.bekidding.com>, David J. Braden
<no...@ugotta.bekidding.com> wrote