+ Reply to Thread
Results 1 to 6 of 6

random numbers without duplicates

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    texas
    MS-Off Ver
    excel 2007
    Posts
    3

    random numbers without duplicates

    Hi.
    I am using excel 2007 and have read how tos over and over on getting a list of random numbers but i guess i am doing something wrong because i keep getting duplicated numbers.
    Is there a simple way to get a list of numbers 1 to 22986 to randomize without repeating ?

    ANY help would be so appreciated.

  2. #2
    Registered User
    Join Date
    07-30-2012
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: random numbers without duplicates

    Quote Originally Posted by squirellyd View Post
    Hi.
    I am using excel 2007 and have read how tos over and over on getting a list of random numbers but i guess i am doing something wrong because i keep getting duplicated numbers.
    Is there a simple way to get a list of numbers 1 to 22986 to randomize without repeating ?

    ANY help would be so appreciated.
    I can think of a way to do it, but it will take two columns...
    in A1 enter the normal RAND function =RAND()
    in B1 enter the following: =RANK(A1,$A$1:$A$22986)+COUNTIF($A1:$A$22986,A1)-1

    Drag both formulas down to row 22986 and you will have random values with no repeats in column B.

    EDIT: Explanation of COUNTIF...
    The COUNTIF resolves the fact that RAND can repeat values and RANK equally ranks matching numbers. This will give duplicates +1 for each duplicate below it in effect filling in the blanks that RANK leaves naturally.

    2nd EDIT: Reversed the order of the variables in the Countif... it should be range first, value second.
    Last edited by biocidej; 01-23-2013 at 05:11 PM.
    Don't forget to mark answered threads SOLVED.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: random numbers without duplicates

    There is a VBA UDF example here:

    http://mcgimpsey.com/excel/udfs/randint.html
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-23-2013
    Location
    texas
    MS-Off Ver
    excel 2007
    Posts
    3

    Re: random numbers without duplicates

    Thank you Tony! I tried what you stated, but alas got duplicates.

  5. #5
    Registered User
    Join Date
    01-23-2013
    Location
    texas
    MS-Off Ver
    excel 2007
    Posts
    3

    Re: random numbers without duplicates

    i got the first sentence then instantly got lost..
    i will accept being talked to like a moron because my brain cells have been fried trying to do this simply

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: random numbers without duplicates

    If you use a UDF, the formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

    The dead simple way is to list the numbers down one column, put =RAND() down an adjacent column, and sort by the random numbers.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1