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

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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value?

I like this method so far.



"joel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

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

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"
<(E-Mail Removed)> wrote:

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

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

On Mar 16, 5:36*pm, Neal Zimm <nealz...@yahoo.com> 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.

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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
What is the best method to make a list of randomized(non-repeating) numbers between a start value and a end value?

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