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

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

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

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

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

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

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

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.

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

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

How can I get this set of formulas for excel 2010

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

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

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?

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…

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

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.

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

mrexcel[dot]com/forum

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?

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!

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

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

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

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

Love is good and Excel is Fun!!

I am glad that it is helpful!!!

You are welcome so much!!