Excel – Random with No Repeats: Episode 1471 – Your Ultimate Guide to Solving Tech Issues | Step-by-Step Troubleshooting for Windows, Mac & More

Channel	Publish Date	Thumbnail & View Count	Actions
MrExcel.com	2011-11-16 15:02:15	108,334 Views	View on YouTube View Channel

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: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

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

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153149/

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!