I need help with random number generation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to generate a random list of the whole numbers 1-52 with no duplicates
and no decimal places.
 
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
 
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
 
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
 
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.
 
Max said:
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()
Click to expand...

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

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

3rd line above should read as:
 
Max said:
In this instance, guess I was worried about any subsequent insertion of row
at the top fubarring the sequential numbering in col B <g>
Click to expand...

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.
 
Very good, Harlan, thanks for the range of efficient alternatives !
Might take a while though, before these get fully assimilated into the
bloodstream here ..
 
Back
Top