I want random numbers generated without repeating

Johncobb

New Member
Joined
Sep 7, 2006
Messages
28
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello

FOr VB version how is this?

Code:
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Barry, I didn't understand the whole relationship until Erik kindly clarified... Now I understand. Makes sense, as does your tweak.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top