Look Who it Was Random video

Excel - Drawing Bingo Numbers without Duplicates - Episode 734

4,732 views · Published 12 February 2009 · 3:22 · Indexed 4 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Drawing Bingo Numbers with Excel.

Welcome back to the MrExcel netcast! In this video, we're going to tackle the problem of generating bingo numbers without any duplicates. As we discussed in our previous video, randomly generating numbers from 1 to 90 can result in duplicates, which is not ideal for a game of bingo. So, I'm going to show you a simple solution to this problem using Excel.

To start off, we'll fill in the numbers 1 to 90 in column E and use the RAND function in column F to generate random numbers. Then, we'll sort the numbers in column F and choose the last number as our bingo number. This way, we can ensure that there are no duplicates in our list. We can then delete the chosen number from the list and repeat the process for the next draw.

Now, let's take a look at the macros involved in this process. First, we'll clear all the cells and add headings for "Number" and "Sort" in cells E1 and F1. Then, we'll use the data series command to fill in the numbers 1 to 90 in cells E2 to E91. Next, we'll add the formula =RAND in cells F2 to F91 to generate random numbers. Finally, we'll use the sort function to choose the last number in column F and copy it to column A, and then delete the chosen number from the list.

Let's see this in action! As we draw the numbers, you'll notice that the list gets shorter and shorter, as we delete the chosen numbers from the list. And after 90 draws, we're left with only one number in the list, just like a real bingo game where someone would have called bingo by now. And for the next game, we can simply start over and repeat the process.

So there you have it, a simple and efficient way to generate bingo numbers without any duplicates. I hope you found this video helpful. Don't forget to subscribe to our channel for more Excel tips and tricks. Thanks for watching and we'll see you in the next netcast from MrExcel!

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) Introduction to the topic of generating bingo numbers without duplicates.
(00:20) Explanation of the problem with randomly generating numbers.
(00:31) Method for eliminating duplicates by filling in numbers.
(00:43) Sorting the numbers and choosing the last number as the chosen one.
(01:07) Explanation of the macros used to generate the numbers.
(01:21) Step-by-step process for generating the numbers.
(02:01) Demonstration of the process in action.
(02:33) 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:
Bingo numbers
Data series command
Deleting values
Drawing random numbers
Excel macros
Generating random numbers
Generating unique bingo numbers
Hamilton's problem
Removing duplicates
Sorting by headers
Sorting data
Using RAND function

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



If you want to randomly choose from a list and never have duplicates, you can use the method discussed in this podcast. Episode 734 shows you how.

More from this channel