How to fill a matrix of 18 x 9 randomly

[Msmits]

Hi,

for a bingo game, I'm looking for a way to randomly fill a matrix of 18 rows by 9 columns with only 90 numbers (1 - 90). This means there are 72 open positions (or filled with zero's).

There are some rules to be applied to fill the matrix:

• Each row can only have 5 numbers
• The first column has 9 numbers
  • in the first column number 1 - 9 are used
• 2nd till 8th column have 10 numbers
  • in the 2nd column number 10 - 19 are used
  • in the 3rd column number 20 - 29 are used
  • ...
• Last column has 11 numbers (numbers 80-90)

Anyone some thoughts on how I can do this?
I can manage to get each row only using 5 number, the right set of number in every column but I cannot manage to get the total number by column correct....

Thanks for your input.
Maarten

 

[Scott Huish]

Umm. 18x9 is 162 positions. Did you mean 8 rows x 9 columns? And if so, I'm assuming you don't want duplicate numbers.

 

[Msmits]

Umm. 18x9 is 162 positions. Did you mean 8 rows x 9 columns? And if so, I'm assuming you don't want duplicate numbers.

Hi Scott,

I really mean 18x9.
example below:
http://www.bingohall.org/wp-content/uploads/2012/02/90-ball-bingo-strip.jpg

regards,
Maarten

 

[Scott Huish]

Oh. I see 72 blank positions. How did you get the across?

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub Bingo18x9()
  Dim R As Long, C As Long, X As Long, Z As Long, RandomIndex
  Dim ColNums As Variant, Tmp As String
  ColNums = [{1,2,3,4,5,6,7,8,9}]
  Range("A1:I23").Clear
  For R = 1 To 21 Step 4
    For Z = 0 To 2
      For X = UBound(ColNums) To LBound(ColNums) Step -1
        RandomIndex = Int((X - LBound(ColNums) + 1) * Rnd + LBound(ColNums))
        Tmp = ColNums(RandomIndex)
        ColNums(RandomIndex) = ColNums(X)
        ColNums(X) = Tmp
      Next
      For C = 1 To 5
        Cells(R + Z, Val(ColNums(C))) = Application.RandBetween(10 * (ColNums(C) - 1) - (ColNums(C) = 1), 10 * ColNums(C) + (ColNums(C) <> 9))
      Next
    Next
    Cells(R, "A").Resize(3, 9).Interior.ColorIndex = 6
  Next
End Sub[/td]
[/tr]
[/table]

 

[mikerickson]

Perhaps

Sub test()
    Dim arrOutPut(1 To 18, 1 To 9) As String
    Dim arrPossible() As String
    Dim i As Long, j As Long, randindex As Long
    Dim temp As String
    
    For i = 1 To 9
        ReDim arrPossible(1 To 18)
        Select Case i
            Case 1
                For j = 1 To 9: arrPossible(j) = j: Next j
            Case 9
                For j = 1 To 11: arrPossible(j) = (10 * (i - 1)) + j - 1: Next j
            Case Else
                For j = 1 To 10: arrPossible(j) = (10 * (i - 1)) + j - 1: Next j
        End Select
        
        For j = 1 To 18
            randindex = WorksheetFunction.RandBetween(1, 18)
            temp = arrPossible(randindex)
            arrPossible(randindex) = arrPossible(j)
            arrPossible(j) = temp
        Next j

        For j = 1 To 18
                arrOutPut(j, i) = arrPossible(j)
        Next j
    Next i
    
    Range("A1").Resize(18, 9).Value = arrOutPut
End Sub

 

[Rick Rothstein]

Perhaps

Sub test()
    Dim arrOutPut(1 To 18, 1 To 9) As String
    Dim arrPossible() As String
    Dim i As Long, j As Long, randindex As Long
    Dim temp As String
    
    For i = 1 To 9
        ReDim arrPossible(1 To 18)
        Select Case i
            Case 1
                For j = 1 To 9: arrPossible(j) = j: Next j
            Case 9
                For j = 1 To 11: arrPossible(j) = (10 * (i - 1)) + j - 1: Next j
            Case Else
                For j = 1 To 10: arrPossible(j) = (10 * (i - 1)) + j - 1: Next j
        End Select
        
        For j = 1 To 18
            randindex = WorksheetFunction.RandBetween(1, 18)
            temp = arrPossible(randindex)
            arrPossible(randindex) = arrPossible(j)
            arrPossible(j) = temp
        Next j

        For j = 1 To 18
                arrOutPut(j, i) = arrPossible(j)
        Next j
    Next i
    
    Range("A1").Resize(18, 9).Value = arrOutPut
End Sub

The OP's first condition was "Each row can only have 5 numbers"... your code does not appear to limit the number of values in any single row to only 5 numbers... I have seen as little as 3 and as many as 7 values on a single row in my limited testing.

 

[mikerickson]

Yes. It would help if the OP posted a sample of what the result might look like.

 

[Rick Rothstein]

Yes. It would help if the OP posted a sample of what the result might look like.

The OP posted a link to a JPEG in Message #3 that shows a sample of the kind of result he was looking for.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub Bingo18x9()
  Dim R As Long, C As Long, X As Long, Z As Long, RandomIndex
  Dim ColNums As Variant, Tmp As String
  ColNums = [{1,2,3,4,5,6,7,8,9}]
  Range("A1:I23").Clear
  For R = 1 To 21 Step 4
    For Z = 0 To 2
      For X = UBound(ColNums) To LBound(ColNums) Step -1
        RandomIndex = Int((X - LBound(ColNums) + 1) * Rnd + LBound(ColNums))
        Tmp = ColNums(RandomIndex)
        ColNums(RandomIndex) = ColNums(X)
        ColNums(X) = Tmp
      Next
      For C = 1 To 5
        Cells(R + Z, Val(ColNums(C))) = Application.RandBetween(10 * (ColNums(C) - 1) - (ColNums(C) = 1), 10 * ColNums(C) + (ColNums(C) <> 9))
      Next
    Next
    Cells(R, "A").Resize(3, 9).Interior.ColorIndex = 6
  Next
End Sub[/td]
[/tr]
[/table]

In looking at your posted example again, I am thinking the above code does not do what you actually want. You said this in your opening message...

• Each row can only have 5 numbers
• The first column has 9 numbers
  • in the first column number 1 - 9 are used
• 2nd till 8th column have 10 numbers
  • in the 2nd column number 10 - 19 are used
  • in the 3rd column number 20 - 29 are used
  • ...
• Last column has 11 numbers (numbers 80-90)

When you said "number # - # are used", I assumed that meant the values for those columns would be chosen from the number range, but that repeats could occur; however, your sample shows each number in the number range is used only once per column. So, when you said number #-# are used, did you mean each number must be used once and only once per column? If so, you should know that the results will not be completely "random" as some manipulation of the values will be needed in order to meet such a restriction... the odds that randomly generated numbers will fall in locations to meet your 5 per row and your use-them-all per column restrictions is virtually nil.