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
About this tutorial: Video duration: 9:20 in this video i show full introduction in excel insert tab in Hindi Learn Excel |...
About this tutorial: Video duration: 1:50 Get to know about easy steps to formula to convert time to seconds in Microsoft® Excel...
About this tutorial: Video duration: 1:21 Watch this video to learn how to freeze the top row so you can still see the column...
About this tutorial: Video duration: 2:20 You can learn Microsoft Excel in just one evening....
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!!