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?

Watch out this
http://www.youtube.com/watch?v=SoK9kq-0uXg

--
Jacob


"The Rook" wrote:

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?

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

The Rook wrote:

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?

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.


"The Rook" wrote:

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?

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

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

"The Rook" wrote:
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.

I wrote:
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 -----

"Joe User" wrote:
"The Rook" wrote:
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.