Generate a Unique List of Random Numbers in Excel With a Simple Formula

[@patrickschardt7724]

Excellent job. Easy to understand. The cool thing about the Microsoft 365 versions is: You can have a large set of master data and use INDEX MATCH or other lookup functions to create random smaller data sets Just match the random number to a position sequence

[@TeachExcel]

Completely agree! And Match really is an amazing function too, even when used in VBA! (Just added some great features to the VBA course I'm finishing, all thanks to Match.)

[@davethomas4181]

Although random numbers are not something I would (typically) use on the job I really appreciate the insights into formula creation. Thank you Don!

[@TeachExcel]

You're very welcome Dave! Oddly enough, this question about random numbers is one of the most common that I get asked, along with drop down lists.

[@IvanCortinas_ES]

A great and complete tutorial. Thank you for sharing it!

[@TeachExcel]

Thanks Ivan! Glad you think so) Next week is the complex formula explanation, so hopefully that will come out well.

[@wayneedmondson1065]

Nice! Cool tricks. Thanks for sharing :)) Thumbs up!!

[@TeachExcel]

Thanks Wayne! :))

[@darrylmorgan]

Some Really Cool Formulas Excellently Explained!Thankfully Microsoft 365 Makes Complex Formulas So Much Easier...Thank You Sir :)

[@TeachExcel]

Thanks Darryl!! And hopefully, you will think the same about the complex formula explanation next week lol.

[@ajpran]

thanks sir, it's works good, but what if with exclude number ?

[@Excelambda]

Great video! Old and new school. A trick that I use when I generate random nr. to make them stop but keep them where they are. For ex: in B2 put "y" , in cell C2 =IF(B2="y",RANDARRAY(10,,1,100,1),C2#) . If we change B2 with "n" , will stop recalculating. New set of data? back to "y". Hit F9...F9....Like to keep a particular set, change B2 to "n" . Obs. If circular reference error, we need to go in Options , Formulas , tick ..Enable iterative calculations. Also could be cool to generate random words, random list selection.😉✌✌

[@abbottkatz8830]

Interesting point! It seems that if you simply leave B2 blank it also won't recalculate.

[@Excelambda]

@@abbottkatz8830 It makes sense, recalculates ONLY for "y", I did it on purpose . Is easy to change for different behavior, as anybody likes.

[@abbottkatz8830]

@@Excelambda Either way, it's a very good tip - I didn't realize circular references could be managed this way.

[@Excelambda]

@@abbottkatz8830 Thanks, is powerful but tricky😉👍

[@TeachExcel]

I love this trick! And I feel sorry for the casual Excel user who opens one of your files on their computer and tries to blindly change some formulas hahaha

[@Kolian1274]

Hello dear Ihave 10 deferent numbers as Inout I want to see how many random number I can create from that 10 number For example my number is 03,05,07,11,13,15,17,19,21,23 Find out how many random number I can creat using above number Thanks

[@UmuokpalaOcha]

It can be a combination of 2 , 3 and 4 digit code

[@yawsarpong4552]

Please help me out; my English is poor but I hope you will understand me afterwards. I have 45 numbers and I want to group them into 3, which is 15 each. Now how can I you use 15/30 randomly.

[@meetmaheshwari617]

Why I am getting a blank?😪

[@kathyroberts7777]

I hope somebody understood what you said, I am sorry I was not one of them! Wayyyy too advanced for me.

[@TeachExcel]

No worries, check this week's tutorial and it might help out a bit! There, I break-down 5 techniques for making advanced formulas and then combine them to make the biggest formula from this tutorial. I'm not saying it is an easy tutorial, but it might help clear things up a little.

[@UmuokpalaOcha]

I want to generate 100000 4 digit unique code