Random Numbers

[The Rook]

I am wanting to generate random numbers from 1 to 20 in cells A1 to A20, but
have no duplicates.

How can I do this?

 

[Jacob Skaria]

Watch out this

 

[Bob I]

place 1 - 20 in column A, in column B put RAND(), select a1:b2, Sort on
column B.

 

[Mike H]

Hi,

You can use this macro

Sub Liminal()
Dim FillRange As Range
Set FillRange = Range("A1:A20")
For Each c In FillRange
Do
c.Value = (20 * Rnd) + 1
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[James Silverton]

Mike wrote on Thu, 25 Mar 2010 07:05:01 -0700:

You can use this macro

Sub Liminal()
Dim FillRange As Range
Set FillRange = Range("A1:A20")
For Each c In FillRange
Do
c.Value = (20 * Rnd) + 1
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

When competing hypotheses are otherwise equal, adopt the
hypothesis that introduces the fewest assumptions while still
sufficiently answering the question.

Come on Mike, William of Ockham said essentially that a long time ago


--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Joe User]

I am wanting to generate random numbers from
1 to 20 in cells A1 to A20, but have no duplicates.
How can I do this?

A variation of Tony Valko's (Biff's) approach:

A1: =RANDBETWEEN(1,20)

A2:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
RANDBETWEEN(1,21-ROWS($A$1:A2)))

Copy A2 into A3:A20.

Note that A2 is an array formula. Enter an array formula by pressing
ctrl+shift+Enter instead of just Enter. In the Formula Bar, Excel will
display the formula enclosed in curly braces, viz. {=formula}. You cannot
enter the curly braces yourself. If you make a mistake, select the cell,
press F2, edit as needed, then press ctrl+shift+Enter.

To avoid having this formula change every time any cell in the workbook is
edited(!), put these formulas in another column, changing $A$1, A1 and A2
appropriately. Then copy the other column, and use paste-special-value to
put the values into A1:A20.

If you get a #NAME error, look at the RANDBETWEEN help page for instructions.

 

[Joe User]

A2:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
RANDBETWEEN(1,21-ROWS($A$1:A2))) [....]
Note that A2 is an array formula.

If you prefer not to use RANDBETWEEN, the following array formula [1] should
work, which is also closer to Biff's paradigm:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
INT((21-ROWS($A$1:A2))*RAND())+1)

However, if we replace RAND() with 0.999999999999999 (15 9s), we will see
that this can return an error because INT unexpectedly returns 20. Actually,
the problem arises if RAND() returns any of the 9 values of the form
0.999999999999999+k*2^-53, for k=0 to 8.

Technically, this should not be a problem in Excel 2003 and Excel 2007
because some time ago, I had determined [2] that the largest RAND() result is
about 0.999999999999964, assuming the constants in KB 828795 [3] are correct.
(The smallest RAND() result is about 0.0000000000000359712259978551).
However, all bets are off with Excel 2010 and later, since RAND() uses a
completely different algorithm.

Some people will be quick to point out that it is extremely unlikely that
RAND() would return a value of 0.999999999999999 or larger anyway. But if we
want to have a bullet-proof formula, we could write the following array
formula [1]:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
MIN(21-ROWS($A$1:A2),
INT((21-ROWS($A$1:A2))*RAND())+1))


-----
Endnotes

[1] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, Excel will display the formula enclosed in curly
braces, viz. {=formula}. You cannot enter the curly braces yourself. If you
make a mistake, select the cell, press F2, edit as needed, then press
ctrl+shift+Enter.

[2]
groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ad5f41d4e55b7992, posted 11 Dec 2009 4:57pm (PT).

[3] support.microsoft.com/kb/828795


----- original message -----