Generate a Unique List of Random Numbers in Excel With a Simple Formula
Create a list of unique random numbers in Excel with a simple formula that is easy to use and change - this works in older versions of Excel as well as Excel 365.
Excel File: www.teachexcel...
Excel Forum: www.teachexcel...
In this tutorial I show you 4 different formulas for generating unique lists of random numbers in Excel - this includes simple methods and more complex methods that allow you to set the minimum and maximum possible values as well as simple methods for simple lists that just need to appear in random order.
Everything in this tutorial should work for Excel versions back to 2007, or at least 2010, but let me know in the comments if you run into any issues.
As usual, make sure to download the file for this tutorial so you can follow along or just copy/paste the formula and use it as you want.
Excel Courses:
- VBA/Macro Course: www.teachexcel...
- Building Professional Forms: www.teachexcel...
TeachExcel.com
#msexcel #howto #tutorial
Пікірлер: 30
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
3 жыл бұрын
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.)
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
3 жыл бұрын
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.
A great and complete tutorial. Thank you for sharing it!
@TeachExcel
3 жыл бұрын
Thanks Ivan! Glad you think so) Next week is the complex formula explanation, so hopefully that will come out well.
Some Really Cool Formulas Excellently Explained!Thankfully Microsoft 365 Makes Complex Formulas So Much Easier...Thank You Sir :)
@TeachExcel
3 жыл бұрын
Thanks Darryl!! And hopefully, you will think the same about the complex formula explanation next week lol.
Nice! Cool tricks. Thanks for sharing :)) Thumbs up!!
@TeachExcel
3 жыл бұрын
Thanks Wayne! :))
It can be a combination of 2 , 3 and 4 digit code
thanks sir, it's works good, but what if with exclude number ?
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
3 жыл бұрын
Interesting point! It seems that if you simply leave B2 blank it also won't recalculate.
@Excelambda
3 жыл бұрын
@@abbottkatz8830 It makes sense, recalculates ONLY for "y", I did it on purpose . Is easy to change for different behavior, as anybody likes.
@abbottkatz8830
3 жыл бұрын
@@Excelambda Either way, it's a very good tip - I didn't realize circular references could be managed this way.
@Excelambda
3 жыл бұрын
@@abbottkatz8830 Thanks, is powerful but tricky😉👍
@TeachExcel
3 жыл бұрын
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
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
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.
Why I am getting a blank?😪
I want to generate 100000 4 digit unique code
I hope somebody understood what you said, I am sorry I was not one of them! Wayyyy too advanced for me.
@TeachExcel
3 жыл бұрын
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.