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




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

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

26 thoughts on “Excel Magic Trick 373: Randomize with No Repeats (Numbers or Words)

    Nusrat Ali

    (January 14, 2018 - 2:06 am)

    Hi I use the same formula in the same way but when I click Ctrl+Shift +entre it puts a bracket on both sides and in the selected cell #Name comes. what am I doing wrong??????????????????? please

    Ben Lim

    (January 14, 2018 - 2:06 am)

    Hi, how can I do this with Microsoft 2016 in Mac? As I can't download the morefunc system. Your help is truly appreciated.

    Stefano Canapone

    (January 14, 2018 - 2:06 am)

    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

    محمد حلمي

    (January 14, 2018 - 2:06 am)

    I have a question
    Why I have not a mrand function at 2007

    Almok Utema

    (January 14, 2018 - 2:06 am)

    The setup file for the more function fails to install for Excel 2013. Is there an alternative?

    Susan Savage

    (January 14, 2018 - 2:06 am)

    Okay just tried this but I don't have an MRAND function in my excel, please Heep with this

    Susan Savage

    (January 14, 2018 - 2:06 am)

    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.

    Sobre Taekwondo

    (January 14, 2018 - 2:06 am)

    Good video! How can I download the pack EMT 376?
    Thanks

    Tushna Mistry

    (January 14, 2018 - 2:06 am)

    Thank you! This was such a simple tutorial on an incredibly useful function. Saved my day!

    Jonathan Rivera

    (January 14, 2018 - 2:06 am)

    How can I get this set of formulas for excel 2010

    Sara Awada

    (January 14, 2018 - 2:06 am)

    can you please tell me where i can get the excel fun pack from ? because i don't have the MRAND function :S on the the RAND

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    @drewfisch09 , Did you use Ctrl + Shift + Enter? And highlight more than one cell?

    drewfisch09

    (January 14, 2018 - 2:06 am)

    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?

    tantopower88

    (January 14, 2018 - 2:06 am)

    why don't I have the "MRAND" function?.. i hate it, coz i really need it..
    i use office 2008 in mac.. i really hope that someone can help me…

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    I do not know how to do it without your friend downloading.

    dsz dsv

    (January 14, 2018 - 2:06 am)

    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.

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    Excel VBA can do it. Try this site for Excel VBA questions:

    mrexcel[dot]com/forum

    skyskid

    (January 14, 2018 - 2:06 am)

    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?

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    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!

    Mynotoar

    (January 14, 2018 - 2:06 am)

    So, what I don't understand is the need of the Index function, does MRAND not deal with words?

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    Great! It is the 4th argument that controls this.

    Helal Ahmed

    (January 14, 2018 - 2:06 am)

    Finally figured it out watching this video, you are great, many thanks

    Helal Ahmed

    (January 14, 2018 - 2:06 am)

    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

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    Love is good and Excel is Fun!!

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    I am glad that it is helpful!!!

    ExcelIsFun

    (January 14, 2018 - 2:06 am)

    You are welcome so much!!

Leave a Reply

Your email address will not be published. Required fields are marked *