I want random numbers generated without repeating

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 7, 2006

• #2

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

O

onlyadrafter

Well-known Member

Joined

Aug 19, 2003

Messages

5,703

Platform

1. Windows

• Sep 7, 2006

• #3

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

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 7, 2006

• #4

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

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 7, 2006

• #5

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)

Click to expand...

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

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 7, 2006

• #6

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

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 7, 2006

• #7

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

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 7, 2006

• #8

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

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 7, 2006

• #9

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

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 7, 2006

• #10

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

 

Upvote 0