Results 1 to 9 of 9

Generate Random Numbers with no duplicates

This is a discussion on Generate Random Numbers with no duplicates within the Excel Questions forums, part of the Question Forums category; I need to generate 5 random numbers from 1 to 15 in cells a3 to a7 with no duplicates. I ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    8

    Default



    I need to generate 5 random numbers
    from 1 to 15 in cells a3 to a7 with
    no duplicates.

    I currently use the code =RANDBETWEEN(1,15)
    in each cell which works great, but it can generate a duplicate number.

    Is there code I can add to =RANDBETWEEN(1,15)
    That would not create a duplicate number
    in cells a3 to a7?
    Or possible VB code to do the same?

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi jmcmahon:

    One way would be to use the MRAND function as ...

    =MRAND(5,,15)

    MRAND is part of the MoreFunc Add-In available from http://longre.free.fr/english

    ******** LANGUAGE="JavaScript" ************************************************************************>
    Microsoft Excel - Book2___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    ****
    2
    ****
    3
    13***
    4
    8***
    5
    7***
    6
    5***
    7
    14***
    8
    ****
    Sheet1*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Jan 2003
    Posts
    8

    Default

    I have Excel 2000, I can't seem to get the
    morefunc addin installed.

    I've tried everything I can think of to get
    it installed and my addins won't recognize it.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,682

    Default

    On 2003-02-15 16:31, jmcmahon wrote:
    I have Excel 2000, I can't seem to get the
    morefunc addin installed.

    I've tried everything I can think of to get
    it installed and my addins won't recognize it.
    Unzip morefunc.zip in a New Folder on your destop.
    Open the folder C:Program FilesMicrosoft OfficeOfficeLibrary in Windows Explorer or via My Computer.
    Drag and Drop the following files from New Folder into ...Library

    MOREFUNC.CNT
    Morefunc.GID
    MOREFUNC.HLP
    Morefunc.xll

    Start Excel
    Activate morefunc via Tools|Add-Ins.


  5. #5
    New Member
    Join Date
    Jan 2003
    Posts
    8

    Default

    I've tried that again and it still doesn't work. When I type in {=MRAND(5,,15)}
    in the formula bar it just gives me ###
    or just show the formula in the cell.

    At this point i'd settle for a vb solution
    instead of an addin.

    I click on tools then addins then I click
    on the box for morefunc (add in functions)
    Then I click on OK then I get nothing.

    When the other add-ins are highlighted, there is a description at the bottom of the add-in, when morefunc is highlighted there is no description at the bottom.

    Could that be a problem?




  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,682

    Default

    On 2003-02-15 17:11, jmcmahon wrote:
    I've tried that again and it still doesn't work. When I type in {=MRAND(5,,15)}
    in the formula bar it just gives me ###
    or just show the formula in the cell.

    At this point i'd settle for a vb solution
    instead of an addin.

    I click on tools then addins then I click
    on the box for morefunc (add in functions)
    Then I click on OK then I get nothing.

    When the other add-ins are highlighted, there is a description at the bottom of the add-in, when morefunc is highlighted there is no description at the bottom.

    Could that be a problem?
    Enter in A1...

    =EVAL(1+5)

    What result do you get?

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi jmcmahon:

    If you have successfully installed the MoreFunc, you enter the following formula ...

    =MRAND(5,,15)

    This is an array formula, and is to be entered with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets are not to be entered manually, those are entered by the system, when the formula is entered as an array formula.

    I hope it makes sense. If you need to discuss this further, please post back and let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Hello Jm:
    If your having trouble with a formula soluton here's a VBA solution for you:

    Public Sub RandNum()
    Randomize
    Cells.Clear
    For Each c In Range("A3:A7")
    TryAgain: c.Value = (Int((15 * Rnd) + 1))
    If Not Range("A2:A" & c.Row - 1).Find(c.Value, LookIn:=xlValues) Is Nothing Then GoTo TryAgain
    Next c
    End Sub

    just paste this in your VBA window and your done.

  9. #9
    New Member
    Join Date
    Jan 2003
    Posts
    8

    Default

    Thanks for the help I appreciate it.

    John

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