Help with "bingo" spreadsheet

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

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)

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.

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 :

Sub test()
Dim rand1(90)
Randomize
For n = 1 To 90
Check1:
rand1(n) = Int((90 * Rnd(100)) + 1)
For F = 1 To T
If rand1(n) = rand1(F) Then GoTo Check1:
Next F
T = T + 1
Next n
For n = 1 To 90
MsgBox rand1(n)
Next n
End Sub

HTH

DominicB

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.

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?