Excel - Random with No Repeats: Episode 1471

Рет қаралды 107,242

Күн бұрын

Microsoft Excel Tutorial: Generating Random Numbers Without Repeats in Excel | MrExcel Podcast.
Welcome to another episode of the MrExcel podcast. In this episode, we tackle a common Excel problem: generating random numbers without repeats. Last week, we covered the basics of the RAND function, but what if you need to ensure that the numbers generated do not repeat? That's where this podcast comes in.
We start off with a simple scenario: choosing six random months from a list of twelve. Using the RAND function, we can easily generate six random numbers, but there's a chance that some of them may be duplicates. So, how do we eliminate those repeats? My method involves using the LARGE function and the VLOOKUP function, and it's a great one to do with my colleague Mike, as there are probably a million ways to achieve this.
First, we use the RAND function to generate a list of random numbers. Then, we use the LARGE function to extract the largest numbers from that list. But instead of typing in the number one, we use the geeky trick of using "row of A1" to represent the number one. This may seem unnecessary, but it will make sense later on. We then use the VLOOKUP function to match those numbers with the corresponding months in our list. And voila, we have six random months without any repeats.
Now, you may be wondering, is this method foolproof? Well, technically, there is a chance that there may be a repeat if you generate a large number of random numbers. But the odds are stacked against it, and it's highly unlikely to happen. And if it does happen, just press F9 to generate a new set of random numbers. So, with this method, you can virtually assure that there will be no repeats.
I hope you found this podcast helpful and that it saves you time and frustration in your Excel tasks. Thank you for tuning in, and be sure to join us for our next netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Random No Repeats
(00:10) Previous Experience with Random Podcast
(00:20) Need for Random Selection without Repeats
(00:33) Using the RAND Function and LARGE function
(00:50) Adjusting for Different Number of Selections
(01:00) Using the VLOOKUP Function
(01:10) Copying Down the Formula
(01:26) Possibility of Repeats
(01:36) Rare Occurrence of Repeats
(01:46) Solution for Repeats
(01:59) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Avoiding repeats in Excel
Episode 1471
Excel tips and tricks
Large function in Excel
Learn Excel podcast
Random no repeats
Random number selection
Random selection of months
VLOOKUP function in Excel
#excel #tutorial
This video answers these common search terms:
Avoiding repeats in Excel
Episode 1471
Excel tips and tricks
Large function in Excel
Learn Excel podcast
Random no repeats
Random number selection
Random selection of months
VLOOKUP function in Excel
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
Choose random weeks from the Data Set, but do it without repeating a week. In a revisit to Episode #1469, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #1471 today to learn the answer and solve the challenge.
Learn Excel 2010 -- "Copy Totals to Week # Macro": Podcast #1470
November 14, 2011 at 1:06 pm MrExcel East Edit
Patrick has a weekly Expense Ledger Spreadsheet set up; he wants to create a Macro to copy the Totals from Report 1 to a corresponding Week Number Row on the Summary worksheet. This Macro has to use the FIND command in VBA to figure out where to paste special the data. Follow along with Episode #1470 as Bill shows us how to accomplish the task.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com - Your One Stop for Excel Tips and Solutions. Visit us today!

Пікірлер: 51

@Mikegaryhouse 5 жыл бұрын

Thank you! Used this to create a random seating plan for my class. Incredibly helpful!

@GeminiZeroX 8 жыл бұрын

You sir, are a magician. Thank you.

@chelseadrysdale2729 2 жыл бұрын

Omfg thank you! I have been struggling with complicated formulas that weren't working and this totally solved my issue, and so easily!

@franciscojunior8066 6 жыл бұрын

Uau! Muito bom. Obrigado professor! Wow! Very good. Thank you teacher!

@lightsonecon4099 4 жыл бұрын

Excellent, and easy. Bada boom! Thanks!

@bl00dlinehindu 11 жыл бұрын

thank a lot. you dont know how much you helped me.

@danielgar 8 жыл бұрын

MANY THANKS FOR THIS!

@carlosalfonso7504 4 жыл бұрын

Thank you so much very helpful!

@carmenesasoares2458 Жыл бұрын

Thanks, it's very helpful to random the survey location

@VICKYROMEO786 5 жыл бұрын

thx you are doing great work

@thepunisher9928 Жыл бұрын

That F4 trick just blew my mind

@MrXL Жыл бұрын

Yes! Big time saver.

@XLnCADMalayalam 4 жыл бұрын

Brilliant!

@bishwajeetchauhan5947 2 жыл бұрын

old is always gold

@spinafire 8 жыл бұрын

Very helpful, works with Google Sheets.

@dilbagkoundal 3 жыл бұрын

One of the Best tip and trick.

@vexpose 10 жыл бұрын

Thanks for this

@mohq9189 10 жыл бұрын

Hi .......How do I prevent repetition when you use a function of random selection

@kolisar101 9 жыл бұрын

Hello..I am trying a function =RANDBETWEEN(1,166) but I keep getting repeats of certain numbers in the column I need random numbers with no repeats....

@leoZ8 5 жыл бұрын

Thks mr..

@LEXOHD 3 жыл бұрын

Thank you

@rupinderbakhshi5393 10 жыл бұрын

Hey, I used this method for three different lists.It went well in two of them but in the third one when i use LARGE() fn ,the last cell of the resulting list of random numbers gives number error '#num!', while rest of the values are fine.Not able to fix it. Please help!

@pimpmyforklift 7 жыл бұрын

Hey mate your a legend! I have Preselected numbers between 1 and 10 for Rand not to duplicate and highlight non duplicates for example. Need this for work cheers :)

@pimpmyforklift 7 жыл бұрын

Bill Jelen Mate thanks heeps for your help and reply . So close to what I'm looking for ! Column A numbers need to be locked and Column D numbers are random with no repeats and decimals. I know how to random no repeat =1+ROUND((RAND()*10),0) but scratching my head how to lock in numbers in Cell A and to be highlighted when matched Cell D. Once again thanks for helping me with my problem :)

@MisterMcCallister 12 жыл бұрын

What do you think about Google Docs Spreadsheet? Will you ever do a tutorial related to this?

@YaniDueri 10 жыл бұрын

thank you for your reply Bill. yes, in fact I need random numbers and there are the same amount of rows. Is there maybe another formula that won't select the largest random numbers? without repeat, of course? Thank you once more. Your podcasts are incredible.

@nickferrante4666 11 жыл бұрын

So I followed everything in the video and it works great. I have a list of contacts and I wanted to be able to pull out a random 15 names to call on a daily basis for sales, is there a way to duplicate their accompanying date (ie. phone number, email, address)?

@YaniDueri 10 жыл бұрын

Q1: When you typed ROW(A1), replacing "1", you haven't locked it, hence when pulling down the next cell will says ROW(A2). is that meant to be that way? I understand the "1" in =LARGE formula shows the largest numbers smaller than 1, right? and if this number changes what does it mean? Q2: I've repeated this, and I only get the random numbers above 0.6 or so. why? does it only select the highest random numbers? I need it to select with equal chances a 0.9993 and a 0.2 for example Thanks a ton!!!

@Phillyp1990 12 жыл бұрын

Great video, but how can i get a Macro to randomly change the content of 1 cell (lets say from a "Y" to a "N") and then every time i press the macro make it randomly change another cell in a specific range, i hope that makes sence. thanks

@amitbhosale4249 5 жыл бұрын

Hi Extremely useful video. I want to arrange these words to a table. Will you please tell me how can I do it.

@krn14242 12 жыл бұрын

Ha, "Never" say Never. thanks bill.

@horvathgabor5485 4 жыл бұрын

Indeed, because basically it can occur 2 same rnd numbers...

@gamezilla 10 жыл бұрын

I am trying to generate a set of numbers example(1-20 3 times) without any repeats with in the 3 sets so set one would be 1-20 no repeats in it. Same with sets 2 and 3. What I am trying to do is come up with a list of these numbers that can represent the random order people would be in line.

@Phillyp1990 12 жыл бұрын

or if thats not possible how do i get it to go down a cell every time i press the macro button, then jump over to another column once the original column ran out of cells to change, once again i hope this makes sence

@armen3543 4 жыл бұрын

Hello, how to setup random number generator avoid using previous numbers in future ? Like I have list of numbers I don't want to use in future need to setup the number generator to give different numbers.

@nicong3491 5 жыл бұрын

Hi, i need help. I followed your tutorial and it worked great, however i have a lucky draw that needs to select 10 names at one go for 20 rounds, How do i eliminate the names that were already chosen? Else It starts to show up at the 3rd re-roll onwards. Need help urgently, thanks..

@MrXL 5 жыл бұрын

So you need 20 rounds of 10 names. Use the method in the video, but ask for 200 results. The first 10 are the winners in Round 1. The next 10 are the winners in Round 2. And so on.

@fkntwizted 5 жыл бұрын

How can you take the list and make it randomly select with no repeats within a week

@weizhe3103 3 жыл бұрын

Hai, a quick question here. Can i generate a random text outside from the row 2 - 13. Which means other than Jan - Dec

@MrXL 3 жыл бұрын

Yes. Let's say you wanted to randomly choose one name from Andy, Barb, Chris, Diane, Ed, Flo, Gary, Hank, Ike, Jared, Kelly, Lou, Mike, Nan, Otto, Paul, Quenten, Raul, Sam, Tracy, Uma, Vera, Will, Xavier, Yazmin, Zeke There are 26 names there. Use =CHOOSE(RANDBETWEEN(1,26), "Andy","Barb","Chris","Diane","Ed","Flo","Gary","Hank","Ike","Jared","Kelly","Lou","Mike","Nan","Otto","Paul","Quenten","Raul","Sam","Tracy","Uma","Vera","Will","Xavier","Yazmin","Zeke") To do this with no repeats, if you have Microsoft 365, you could put the names in Z1:Z26 and use =SORTBY(Z1:Z26,RANDARRAY(26)). Or, I suppose you could do =SORTBY({"Andy","Barb","Chris","Diane","Ed","Flo","Gary","Hank","Ike","Jared","Kelly","Lou","Mike","Nan","Otto","Paul","Quenten","Raul","Sam","Tracy","Uma","Vera","Will","Xavier","Yazmin","Zeke"},RANDARRAY(26)).

@whatyouwantmetodo8536 7 жыл бұрын

Hi Bill , thanks for your videos very helpful but I am a bit troubled with problem : I need to find all possible rows created by shuffling or randomizing numbers between 1-3 I.e 1,2 and 3 in thirteen columns with no repeat and get as much rows as possible with no repeats. Please help I will reward up to $1000

@pequodexpress 3 жыл бұрын

How would you adjust the formula for just one month showing up randomly without repetition until all the months have been selected at least once?

@MrXL 3 жыл бұрын

If you are using Microsoft 365 and you have the new SEQUENCE function, you could use this: =TEXT(DATE(2021,SORTBY(SEQUENCE(12),RANDARRAY(12)),1),"MMMM")