Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

I want random numbers generated without repeating

This is a discussion on I want random numbers generated without repeating within the Excel Questions forums, part of the Question Forums category; In my worksheet in column CC in C1:C20 I want Excel to ganerate 20 random numbers between 1 and 80 ...

  1. #1
    New Member
    Join Date
    Sep 2006
    Location
    Sydney
    Posts
    28

    Default I want random numbers generated without repeating

    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.

  2. #2
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,651

    Default

    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?
    (XL2010 on Windows 7 Professional SP 2)

  3. #3
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    5,470

    Default

    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
    -------------------------
    Hope this is helpful.
    -------------------------

    Have been away from the forum for quite a while, but am now back.

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,527

    Default

    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)

  5. #5
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,651

    Default

    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?
    (XL2010 on Windows 7 Professional SP 2)

  6. #6
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,829

    Default

    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
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,527

    Default

    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

  8. #8
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,651

    Default

    Barry, I didn't understand the whole relationship until Erik kindly clarified... Now I understand. Makes sense, as does your tweak.
    (XL2010 on Windows 7 Professional SP 2)

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,527

    Default

    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)

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    78,868

    Default

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

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com