Best Method

D

Dennis Tucker

What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value?
 
J

joel

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.
 
L

L. Howard Kittle

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
What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value?
 
M

marcus

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
 
N

Neal Zimm

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.
 
J

Jef Gorbach

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.
Click to expand...

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.
 
H

helene and gabor

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?


What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value?
 
D

Dana DeLouis

What is the best method to make a list of randomized(non-repeating)
numbers between a start value and a end value?
Click to expand...


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
 
Top