Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
The best method is to use a 2 dimensional array (or two columns in a spreadsheet). put the numbers you want to sort in one column and then put a random number in the 2nd column. Finaly sort the 2 columns by the random number column. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187459 http://www.thecodecage.com/forumz/chat.php |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
You could try something like this, will give a unique list of random numbers between 1 and 32 listed in A1A20.
Change the c.Value = to suit the range of start and end numbers and adjust the range for the list to suit. Do not know if it is the best as you request. Sub sonic1to32() Dim FillRange As Range, c As Range Set FillRange = Range("A1:A20") For Each c In FillRange Do c.Value = Int((32 * Rnd) + 1) Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2 Next End Sub HTH Regards, Howard "Dennis Tucker" wrote in message ... What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
I like this method so far.
"joel" wrote in message ... The best method is to use a 2 dimensional array (or two columns in a spreadsheet). put the numbers you want to sort in one column and then put a random number in the 2nd column. Finaly sort the 2 columns by the random number column. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187459 http://www.thecodecage.com/forumz/chat.php |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
Hi Denis
Say your start value is 10 and your end value is 20. This formula will give you non matching numbers between these two points. =RAND()*(20-10)+10 Take care Marcus |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
See http://www.cpearson.com/excel/RandomNumbers.aspx for code for generating a set of random longs between a lower and upper bound, either with repetition or without repetition. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 14 Mar 2010 16:14:09 -0700, "Dennis Tucker" wrote: What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
Dennis,
I'm just a bystander here, but from an app point of view, If you want random numbers, Why do they have to be non- repeating ? Guessing that you're not really after a random number at all. The msoft help on the Rnd function tells how to generate the "same" sequence of random numbers (if needed) but there's no guarantee the a provided number won't be the same as the one previously generated. -- Neal Z "Dennis Tucker" wrote: What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
On Mar 16, 5:36*pm, Neal Zimm wrote:
Dennis, * I'm just a bystander here, but from an app point of view, If you want random numbers, *Why do they have to be non- repeating ? *Guessing that you're not really after a random number at all. For those situations where you need to randomly draw from a fixed number of unique items, which once drawn cant be reused/repeated -- like randomly assigning people to teams, bingo numbers, etc. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
Hello,
=randbetween(1,36) will for example generate random integers from 1 to 36. When you copy this formula down along a column, you could have as many such numbers as you want. You can then copy this column to another set of cells and use: Remove Duplicates feature of Excel 2007. Good Luck! Gabor Sebo ----- Original Message ----- From: Dennis Tucker Newsgroups: microsoft.public.excel.programming Sent: Sunday, March 14, 2010 7:14 PM Subject: Best Method What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value? "Dennis Tucker" wrote in message ... What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Method
On 3/14/2010 7:14 PM, Dennis Tucker wrote:
What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value? Hi. Just as a side note, the "Best Method" may 'depend' on your size also. Most algorithms first generate all numbers, then pick unique values. However, this may be impractical if you have to pick from a size of 1 million. Not quite what I use, but an algorithm may want to branch off and do something like this if you had to pick 6 numbers from 10,000,000 Sub Demo() [A1:A6] = WorksheetFunction.Transpose(RandomSample(6, 1, 10000000)) End Sub Function RandomSample(n, L, H) Dim d Dim x Set d = CreateObject("Scripting.Dictionary") Do While d.Count < n x = Int(Rnd * (H - L + 1)) + L If Not d.exists(x) Then d.Add x, x Loop RandomSample = d.keys End Function However, your main program would not want to call this routine if you wanted to pick 100 numbers out of 100. The program would have to loop, on average, 518 times. Hence, the above would not be efficient. = = = = = = = HTH :) Dana DeLouis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method "Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Please post this thread a correct full method, method about | New Users to Excel | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
GetObject method not work after Call Shell Method | Excel Programming | |||
Why QUIT method doesn't work after COPY method? | Excel Programming |