Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi To All,

    For a set of thirty samples I must construct a "random order" in which to run tests. The numbers to be randomized are 1,2,3,4,......36, and of course each number must be unique, cannot be used more than once. Do in need VBA to do this or is there a easier formula.

    Thanks for your help....great resource

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Marty,

    I don't know if this can be done w/o VBA but here is a cut & paste ready solution.

    Upon further inspection this solution may take some minor editing for your purposes but it has the basics.

    Call: =RandLotto(1, 36, 36)
    This will return your 36 random numbers as a string in the cell you place the formula in.

    This routine could be easily modified to place the numbers in separate cells. Let me know if you need help.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Why not put the numbers 1 to 36 in one column
    Then the random function in a second which you can then convert to values (they will be unique)
    Then sort the list based upon the rand column

    [attachment=89399:RandNums.jpg]

    If you want it totally random leave the rand() column as a function
    Attached Images Attached Images
    Andrew

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    very elegant!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Good Morning Andrew and RG,

    Wow, ask and you shall receive.....both will do the job. As usual this site exceeds expectations, an excellent learning tool for all. I'm coming to the VBA table a little late but is powerful stuff

    Thanks Again,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by AKW View Post
    they will be unique
    Andrew,

    I seem to remember Excel having a problem with Rand not returning consistent unique numbers - although this was while I was still working - 10 yrs ago. Has that been fixed?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I believe it was meant to be fixed in one of the 2003 Service Packs.
    To be honest, with 36 values it is unlikely to be an issue even if it wasn't.

    There was an issue with Random numbers being generated which I think was fixed way back.
    It also needed a lot of recalcs on a lot of cells.

    MS have this article on the subject Random Nos MS
    Andrew

  8. #8
    New Lounger
    Join Date
    May 2010
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've found the random number functions available from http://www.ntrand.com/ to be very powerful (and FREE!!). Richard.

Posting Permissions

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