Help with "bingo" spreadsheet

**Riley_5000** · 12-15-2008, 01:41 AM

Hi

I'm your average excel user (I can record a macro and use most common functions).

Recently, being bored, I developed a sudoku solver spreadsheet and a lotto spreadsheet that I showed off to all and sundry.

Now my girlfriend has challenged me to come up with a Bingo spreadsheet. Well, I've tried . . .But I don't think you do it with functions, and I'm not savvy enough to do it with code.

Basically, in Bingo you have 89 numbers. I need to display 1 number at a time randomly chosen from the 89 available, but I must display each number only once, and therefore I must do it a maximum of 89 times to display all 89 numbers.

I can generate the random numbers (obviously), but I'm stuck as to how to display each number only once.

Please help!!
Thank you

**JBeaucaire** · 12-15-2008, 03:22 AM

CPearson has created a solution for this very thing. Read up on it here. Let us know how it goes.

http://www.cpearson.com/excel/RandomNumbers.aspx (Getting An Array Of Unique, Non-Duplicated Values)

**Riley_5000** · 12-15-2008, 04:36 AM

Thanks, I'll give it a try

**Riley_5000** · 12-15-2008, 06:07 AM

I've had a look at it. Unfortunately, it returns a range of non - duplicate values between 2 numbers you specify. This isn't quite right . . .

I need to display just one number, and that one number musn't be repeated until I've gone through all 89.

It's very tricky. I initially wrote a code which generates a random number (randbetween(1,89)), vlookups that number in my table (1 - 89), and then deletes the number from my list. Well, that certainly worked, except that as more numbers got deleted, more vlookups returned blank.

Please help??

**JBeaucaire** · 12-15-2008, 12:17 PM

Post up what you did and let's see what's up. You're supposed to be able to generate any number of random numbers you want, including all 89.

**dominicb** · 12-15-2008, 12:32 PM

Good afternoon Riley_5000

I've written a bingo type game - you can find it on my add-in (see the link below). I found that the easiest way to do this, is write a little routine that will generate all numbers first, pile them all into an array, and then start going through the array one by one.

Have a look at the code below - it does just that and then flash each one up in a MsgBox. It might be just enough to get you going :

Please Login or Register  to view this content.

HTH

DominicB

**shg** · 12-15-2008, 12:33 PM

Just put the 89 numbers in random order (or use Chip's routine) in some hidden place, and then index them one at a time.

**ChemistB** · 12-15-2008, 01:23 PM

To create a non-duplicating set of random numbers, I typically use the RAND() (which rarely will produce a duplicate) and then Rank them. See attachment. Is this a good starting point for you?

**Riley_5000** · 12-16-2008, 04:26 AM

Thanks everybody, I'll give all of this a try.

I've deleted my pathetic attempt, however I can rewrite this week and post it if anybody's curious

**Riley_5000** · 12-17-2008, 12:49 AM

I've tried them all, DOMINI CB came the closest - however, the numbers still occasionally repeat!

I think this should go up on the challenge board.
How about this:
You generate a random number bet 1 & 89, place that number into a table, and then have the code check the table and keep generating numbers until it gets one that isn't there. And then repeat until table is full . . .

That probably requires code - which is not my forte. Can somebody help, or do you think we should put this up on the challenge board??

**dominicb** · 12-17-2008, 06:57 AM

Hi Riley_5000

Originally Posted by Riley_5000

I've tried them all, DOMINI CB came the closest - however, the numbers still occasionally repeat!

Are you sure you have some repeats?

I have just replaced the last three lines before the "End Sub" statement with this :

Please Login or Register  to view this content.

This will place the 90 numbers starting from row 1 in whatever column is active. I ran the macro 10 times in all and each time I got a perfect set of the numbers 1 - 90 in random order.

HTH

DominicB

**Riley_5000** · 12-18-2008, 04:47 AM

yeah, i tried the column code dominic, and checked it for unique values and it always returns a perfect 90. Weird. If u generate just one number at a time and write them down, then after 10 or 20
Numbers u get a repeat. . . I understand ur code, but something's just a little bit. . . . I'm going to try it with just functions, wil u help me with vba, dominic?

**dominicb** · 12-18-2008, 06:25 AM

Hi Riley

Originally Posted by Riley_5000

If u generate just one number at a time and write them down, then after 10 or 20
Numbers u get a repeat. . . . I'm going to try it with just functions, wil u help me with vba, dominic?

I've just used my original code to go through an entire sequence from 1 to 90 twice without a single skip or duplicate. Twp p[erfect runs - exactly as you'd expect if writing them straight to a spreadsheet works. They're from exactly the same array so the two routines couldn't possibly produce different results. Once the routine has been run the numbers are in there.

Of course, I'll continue to help as much as I can whether you use my suggestion or one of the others, as will anyone else who drops back in on this thread.

I don't know if you tried downloading the add-in, but if you want the module and userform that drives the bingo game then I can forward this in a workbook for you. If so PM me your e-mail address.

HTH

DominicB