I need help with random number generation

[David Stoddard]

I need to generate a random list of the whole numbers 1-52 with no duplicates
and no decimal places.

 

[Harald Staff]

Hi

Numbers 1 to 52 in range A1:A52
Formula =RAND() in range B1:B52.
Sort the list by B column.

HTH. Best wishes Harald

 

[Max]

Just another quick way to play with ..

Put:
in A1: =INDEX(B:B,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0))
in B1: =ROWS($A$1:A1)
in C1: =RAND()

Select A1:C1, fill down to C52

Col A will return a random list of all the numbers 1-52 in col B with no
duplicates

Just tap / press F9 to generate a new randomized list in col A

Copy col A and paste special as values elsewhere if needed

Note that you can replace the formulas in B1:B52 with any list of items
(text phrases, alphanumerics etc) which you want to randomize

 

[JulieD]

Hi David

(this might be overkill ....) however, this code doesn't specifically check
for duplicates (it was written for a situation where duplicates were
allowed), but in my tests of generating 52 random numbers between 1 and
10000 it didn't come up with any duplicates.
---------
Sub genrand()

Dim numvals As Long
Dim destcell As String
Dim nummin As Long
Dim nummax As Long
Dim mynums() As Long

nummin = InputBox("What is the minimum number you want to allow?", "min
number", 1)
nummax = InputBox("What is the maximum number you want to allow?", "max
number", 10000)
numvals = InputBox("How many numbers do you want to generate?", "numbers
to generate", 52) - 1
destcell = InputBox("What is the cell reference of where you want the
numbers to go?", "destination cell", "A1")

ReDim mynums(numvals)
j = 0
For i = 0 To numvals
Randomize
mynums(i) = Int((nummax - nummin + 1) * Rnd + nummin)
j = j + mynums(i)
Next

Range("" & destcell & "").Select
For i = 0 To numvals
ActiveCell.Value = mynums(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub
----------

If you need help implementing it please post back

Cheers
JulieD

 

[Ragdyer]

You're actually looking for a random *order* display generator.

You can place the Rand() function in an "out-of-the-way" location of your
sheet.
Say starting in Z1,
=RAND()
And copy down to Z52.

Then, enter this formula into any other column, and copy down 52 rows:

=INDEX(ROW($A$1:$A$52),RANK(Z1,$Z$1:$Z$52))

Hit <F9> for a new random order.

 

[Bernd Plumhoff]

Hello David,

See for example: www.sulprobil.com

HTH,
Bernd

 

[Harlan Grove]

Just another quick way to play with ..

Put:
in A1: =INDEX(B:B,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0))
in B1: =ROWS($A$1:A1)
in C1: =RAND()

Why =ROWS($A$1:A1) rather than =ROW(A1)? You prefer extra typing and
unnecessarily long formulas that take up more storage than necessary?

 

[Max]

Why =ROWS($A$1:A1) rather than =ROW(A1)?

In this instance, guess I was worried about any subsequent insertion of row
at the top fubarring the sequential numbering in col B <g>

 

[Max]

In this instance, guess I was worried about

any subsequent insertion of row at the top
fubarring the sequential numbering in col B <g>

3rd line above should read as:

 

[Harlan Grove]

In this instance, guess I was worried about any subsequent insertion of row
at the top fubarring the sequential numbering in col B <g>

If so, you're not considering rows inserted below row 1 but above the
bottommost row containing such formulas. However, the most robust way of
generating a range of shuffled integers would be to use a range of cells
filled with =RAND() formulas, in the OP's case C1:C52, then use a simpler
formula in column A, either

A1:
=RANK(C1,C$1:C$20)

or

A1:
=COUNTIF(C$1:C$20,">="&C1)

or

A1:
=SUMPRODUCT(--(C$1:C$20>=C1))

There's no need for the inefficient MATCH(SMALL(x,ROW(S)_formula),x,0)
expression. Further, all 3 of the alternatives above can accommodate 2D x
ranges, which MATCH can't, though that may not be relevant to the OP's
situation.

 

[Max]

Very good, Harlan, thanks for the range of efficient alternatives !
Might take a while though, before these get fully assimilated into the
bloodstream here ..