I want random numbers generated without repeating

Johncobb

New Member
Joined
Sep 7, 2006
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Barry, I didn't understand the whole relationship until Erik kindly clarified... Now I understand. Makes sense, as does your tweak.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,214,684
Messages
6,120,874
Members
448,993
Latest member
InquisitiveFrog

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
Back
Top