Best Method

[Dennis Tucker]

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

 

[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?

 

[Dennis Tucker]

I like this method so far.

 

[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

 

[Chip Pearson]

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

 

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

 

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

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.

 

[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?

 

[Dana DeLouis]

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