+ Reply to Thread
Results 1 to 9 of 9

random numbers without dublicates

  1. #1
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    random numbers without dublicates

    how to genarate at A1:A18 random numbers from 1-36 withoutb dublicates.thanks !

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: random numbers without dublicates

    I tend to use a shuffling technique for this:

    1) Generate 36 random unique numbers. I usually use the RAND() function for this. =RAND() into B1 and copy down to B36.
    2) Now "rank" the random numbers in either small to large or large to small using the RANK() function. =RANK($B$1:$B$36,B1) and copy/paste/fill down to A18.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: random numbers without dublicates

    great,but is there any formula which doing it direct?

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: random numbers without dublicates

    Hi,

    In A1:

    =RANDBETWEEN(1,36)

    In A2, array formula**:

    =INDEX(MODE.MULT(IF(ISNA(MATCH(ROW($1:$36),A$1:A1,{0,0})),ROW($1:$36))),RANDBETWEEN(1,COUNT(1/ISNA(MATCH(ROW($1:$36),A$1:A1,0)))))

    and copied down to A18.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: random numbers without dublicates

    thanks so much,i put the formula in the attached workbook,could you please check it cause there is something that i dont understand?
    Attached Files Attached Files

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: random numbers without dublicates

    You haven't put the formulas in the cells I specified. I don't know why you've put them in column B. The first formula was intended for A1 only, the second for A2:A18.

    Regards

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: random numbers without dublicates

    Another one

    D2:D19

    =AGGREGATE(15,6,ROW(D$1:D$36)/ISNA(MATCH(ROW(D$1:D$36),D$1:D1,)),RANDBETWEEN(1,ROWS(D2:D$19)))

  8. #8
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: random numbers without dublicates

    thanks for your help my friends.actually i wanted something else and maybe i did not explained more clear.
    i need to extract 18 unique numbers out of 36 ,each of a time becuasei want to make alot of groups combinations of 18 unique numbers

  9. #9
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: random numbers without dublicates

    yes good,what we need is 18 uniwue numbers each time not the same numbers with different order,thanks boss

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filtering random numbers without regenerating random numbers
    By ptack in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-07-2022, 04:03 PM
  2. [SOLVED] generate random numbers in random range vba
    By hsnfifo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2020, 12:59 PM
  3. Replies: 2
    Last Post: 07-24-2018, 03:47 AM
  4. [SOLVED] macro created buttons not working, random numbers not random
    By dareeldill in forum PowerPoint Programing
    Replies: 4
    Last Post: 07-01-2017, 09:16 AM
  5. Generating a list of Random numbers of random length...
    By Growl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 12:10 PM
  6. [SOLVED] Non-random numbers generated by excel's data analysis random gener
    By Allie in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-17-2005, 02:05 AM
  7. [SOLVED] Re: Non-random numbers generated by excel's data analysis random gener
    By Harlan Grove in forum Excel General
    Replies: 2
    Last Post: 09-13-2005, 12:05 PM

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