# I want random numbers generated without repeating

#### Johncobb

##### New Member
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?
Ragards to all supporters.
John.

### Excel Facts

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?

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

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)

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?

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

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

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

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)

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

Replies
5
Views
368
Replies
3
Views
365
Replies
5
Views
877
Replies
3
Views
728
Replies
11
Views
885

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.

### Which adblocker are you using?

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

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