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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Yesterday we talked about Hamilton's problem of generating bingo numbers.
And I said you know the big problem with just randomly generating 1 to 90 every time is you're going to have duplicates and my general approach when I want to get rid of the duplicates is to fill in the numbers, where that I want to draw from so for example, Cells 1 to 90 and so here in column E , I have all the possible bingo numbers 1 to 90 and in column F, I just have a series of RAND functions. RAND functions.
Basically what I'm going to do is sort by column F and whichever number either comes first or last is the number that we would choose.
In this case I'm going to take the last number so in theory would take that 8 that's down there in cell E91 and copy that up to cell A2 and now we could just delete that from the list to the next time that we draw, we would be basically sorting the remaining numbers.
And so to go back to Hamilton's question of how to do this. It's a couple of short little macros, not as short as yesterday, but let's kind of run through what happens in.
Start over first of all I clear all the cells and then build my headings in E1 and F1.
the headings for a number and sort.
Put in the number 1 and E2 and then use the data series command to fill in E2:E91 that gets me all the numbers from 1 to 90 and then an F-2:F91.
Put in my formula =RAND And now basically every time we calculate we're going to get new random numbers.
So the action of drawing is pretty simple.
We just choose cell F1 and sort the entire region by cell F1 saying that we have headers.
Down here. We then go to somewhere way below our data in this case. E100 is simple.
Hit the end key and the up arrow key and that will get us through the last number in the series and it's going to be a random number every time.
so I copy that and move it over to column A using the same technique we used yesterday.
And then finally delete the last values that we happen to have.
So every time we're going to get a new random number Let's try it.
Draw 70 is the first number chosen and then 21 and then 76 and then 82.
And what you can't see because it's down below the end of the screen, is that every time we draw, we are losing a few numbers off the end.
If we would go ahead and run this 90 times and actually will cut this out. We'll come back.
Okay, there we go.
We just randed. A whole bunch of time you see that every time that we choose a new random number, it's taking the number off the bottom and we're getting a shorter and shorter list down to the point, where we only have one ball left in the bingo cage by now someone should have called bingo, and ofcourse for the next game we would just start over.
So there you have it. A pretty easy way to do bingo numbers making sure that you get no duplicate numbers as you draw.
Well, thanks for stopping by. Will see you next time for another netcast from MrExcel.
Yesterday we talked about Hamilton's problem of generating bingo numbers.
And I said you know the big problem with just randomly generating 1 to 90 every time is you're going to have duplicates and my general approach when I want to get rid of the duplicates is to fill in the numbers, where that I want to draw from so for example, Cells 1 to 90 and so here in column E , I have all the possible bingo numbers 1 to 90 and in column F, I just have a series of RAND functions. RAND functions.
Basically what I'm going to do is sort by column F and whichever number either comes first or last is the number that we would choose.
In this case I'm going to take the last number so in theory would take that 8 that's down there in cell E91 and copy that up to cell A2 and now we could just delete that from the list to the next time that we draw, we would be basically sorting the remaining numbers.
And so to go back to Hamilton's question of how to do this. It's a couple of short little macros, not as short as yesterday, but let's kind of run through what happens in.
Start over first of all I clear all the cells and then build my headings in E1 and F1.
the headings for a number and sort.
Put in the number 1 and E2 and then use the data series command to fill in E2:E91 that gets me all the numbers from 1 to 90 and then an F-2:F91.
Put in my formula =RAND And now basically every time we calculate we're going to get new random numbers.
So the action of drawing is pretty simple.
We just choose cell F1 and sort the entire region by cell F1 saying that we have headers.
Down here. We then go to somewhere way below our data in this case. E100 is simple.
Hit the end key and the up arrow key and that will get us through the last number in the series and it's going to be a random number every time.
so I copy that and move it over to column A using the same technique we used yesterday.
And then finally delete the last values that we happen to have.
So every time we're going to get a new random number Let's try it.
Draw 70 is the first number chosen and then 21 and then 76 and then 82.
And what you can't see because it's down below the end of the screen, is that every time we draw, we are losing a few numbers off the end.
If we would go ahead and run this 90 times and actually will cut this out. We'll come back.
Okay, there we go.
We just randed. A whole bunch of time you see that every time that we choose a new random number, it's taking the number off the bottom and we're getting a shorter and shorter list down to the point, where we only have one ball left in the bingo cage by now someone should have called bingo, and ofcourse for the next game we would just start over.
So there you have it. A pretty easy way to do bingo numbers making sure that you get no duplicate numbers as you draw.
Well, thanks for stopping by. Will see you next time for another netcast from MrExcel.
