Excel Magic Trick 373: Randomize with No Repeats (Numbers or Words)

26
12




About this tutorial:

Video duration: 5:19
See how to select 3 numbers from 50 with no repeats. See how to select 3 names from a list of 10 with no repeats. See the MRAND, INDEX and COUNTA functions.
Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions

Take Sample without repeats

26 COMMENTS

  1. Hi, if you can't use MRAND (it's by far a more efficient and elegant approach)

    in B11 ( B10 does not house any number)

    =IFERROR(AGGREGATE(15,6,ROW($1:$50)/(COUNTIF($B$10:B10,ROW($1:$50))=0),RANDBETWEEN(1,50-COUNT(B$10:B10))),"")

    or using Excel 2007

    =IFERROR(SMALL(IF(COUNTIF($B$10:B10,ROW($1:$50))=0,ROW($1:$50)),RANDBETWEEN(1,50-COUNT(B$10:B10))),"")

    Array entered (to be confirmed with control+shift+enter)

    Just for fun

  2. Thank you, I will be back for more.

    Been looking to do random names in selected cells forever.

    The one other thing I've been looking for, is transferring a list of names to use in a letter, to personalize same letter with each name.

  3. ExcellsFun…..I downloaded the pack and have the mrand function but no matter what I do I get the #NUM! error returned to me. I typed in exactly how you did. I want to get a number from 1-30 without repeats and just have one number at a time appear. But no matter what I Do it just gives me the #NUM! after I press enter after entering the function. Can you provide some help?

  4. HI great video.
    I have a question. I have the MoreFunc Excel add-in and used this fucntion, which is fine. However, I want to sent the file so my friend can use. Is there a way he can use this new function without downloading the add in pack also? I thought saving the file as .xlam did this???
    Thanks.

  5. Thank you for this great instructional video! I found it very useful!

    I want to use this in a classroom setting where I randomly call on students and don't ask the same student twice.

    Can you have it select one name without repeating? (By this, I mean, can you use this with only one cell and have it so that every time it re-calculates it will remember what it picked?) Or is this beyond the functions of excel?

  6. No. MRAND spits out numbers.

    What!? You don't know what INDEX does? INDEX is probably #4 on the all-time list of Excel functions (SUM, VLOOKUP, IF, INDEX).

    INDEX is mind-blowing in what it can do.

    Immediately:

    Go to the Highline Excel Class Playlist and watch #43, 44, and 45!

    After you are done, your Excel glasses will be beneficially altered to properly see INDEX as a big chunk of the Excel magic!

  7. Help required, for some reason, i have done the MRAND function but when ever i enter a value or anything in another cell and press enter, it randomly changes the cells i selected for the MRAND functions to work on but i dont want that to change. I thought only F9 would randomly change the scenario but there appears to be a fault or im doing something wrong please advise

LEAVE A REPLY

Please enter your comment!
Please enter your name here