I want random numbers generated without repeating

[Johncobb]

In my worksheet in column CC in C1:C20 I want Excel to ganerate 20 random numbers between 1 and 80 without repeating the same numbers.
At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.

=INT(RAND()*(80-1)+1)

How can I change the formula or replace it?
Thank you in advance for your help.
Ragards to all supporters.
John.

 

[hatman]

Welcome to the board!

Excel is not capable of "remembering" what values have been generated in the past unless you use VBA (I take that back, it can be done with Smoke and Mirrors, but it's not worth the effort). Is a VBA solution acceptable for you?

 

[onlyadrafter]

Hello

FOr VB version how is this?

Dim MY_RND_NO(80) As Variant
Sub CREATE_RANDOM()
Randomize
Range("C1:C20").ClearContents
MY_COUNT = 1
Do Until MY_COUNT = 21
    NEW_NUMBER = Int(Rnd() * (80 - 1) + 1)
    If MY_RND_NO(NEW_NUMBER) <> "USED" Then
        Range("C" & MY_COUNT).Value = NEW_NUMBER
        MY_RND_NO(NEW_NUMBER) = "USED"
        MY_COUNT = MY_COUNT + 1
    End If
Loop
End Sub

 

[barry houdini]

If you put the formula =RAND() in first row of an unused column, e.g. Z1 and copy down 80 rows (to Z80) then use this formula in C1 copied down to C20

=RANK(Z1,Z$1:Z$80)

 

[hatman]

If you put the formula =RAND() in first row of an unused column, e.g. Z1 and copy down 80 rows (to Z80) then use this formula in C1 copied down to C20

=RANK(Z1,Z$1:Z$80)

I tried this, and still got repeats.... am I missing something?

 

[erik.van.geit]

Hi,

this doesn't have repeats
5 out of 20
you don't need column C, which checks for uniques
   A        B      C     D  E                  
 1 hidden   hidden check    5 between 1 and 20 
 2 0,549178 15     1        15                 
 3 0,503955 12     1        12                 
 4 0,689526 16     1        16                 
 5 0,04452  2      1        2                  
 6 0,444818 10     1        10                 
 7 0,770576 17     1                           
 8 0,039569 1      1                           
 9 0,823062 18     1                           
10 0,505348 13     1                           
11 0,236662 6      1                           
12 0,465127 11     1                           
13 0,221947 4      1                           
14 0,949055 20     1                           
15 0,882001 19     1                           
16 0,339205 7      1                           
17 0,546504 14     1                           
18 0,431017 9      1                           
19 0,232783 5      1                           
20 0,371848 8      1                           
21 0,126138 3      1                           

sheet2
[Table-It] version 05 by Erik Van Geit

RANGE   FORMULA (1st cell)
A2:A21  =RAND()
B2:B21  =RANK(A2,$A$2:$A$21,1)+COUNTIF($A$2:A2,A2)-1
C2:C21  =COUNTIF($B$2:$B$21,B2)
E2:E6   =B2

[Table-It] version 05 by Erik Van Geit

kind regards,
Erik

EDIT: changed table 5 minutes after posting

 

[barry houdini]

With the formula I posted there is a theoretical possibility of repeats but that could only happen if RAND() generated the exact number twice (to 15 decimal places) which is very unlikely. If you want to guarantee absolutely no repeats change to

=RANK(Z1,Z$1:Z$80)+COUNTIF(Z$1:Z1,Z1)-1

 

[hatman]

Barry, I didn't understand the whole relationship until Erik kindly clarified... Now I understand. Makes sense, as does your tweak.

 

[barry houdini]

Note: probably now a moot point but the formula

=INT(RAND()*(80-1)+1)

will only ever generate 1-79, if you want to generate 80s also it would need to be

=INT(RAND()*80+1)

 

[Aladin Akyurek]

Also, if you have installed the morefunc.xll add-in, you can invoke MRAND()...