Bingo word boards random

[reppert25]

Need some help figuring out how to either make up equations or vba code to figure out 1200 combinations of 43 sets of words.
What I am trying to do is merge the data into indesign on a bingo boards, 25 spaces and the center is common, so 24 boxes.
I need 24 columns and each row would be made up of a combination of the 43 sets of words below.
None of the rows can be the same. So pretty much I want the first cell to pick a random item from the list, take it away from the list, then pick another, up to the 24th cell.
Then the next row would do the same with the same 43 sets but when its done if it matches a row above it would start over again until it had 24 cells that have not shown up yet.
I would like to do this for 1200 cards but that could change to x amount less or more.
Thanks for any help.

Here is the list:

[TABLE="width: 235"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]List of words[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Polar Bear[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mom and Cub[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bears Sparring[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Red Fox [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Arctic Fox[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Caribou[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Buggy Love[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Arctic Hare[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Common Raven[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Ermine[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Marten[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Weasel[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Willow Ptarmigan[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Gyrfalcon[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Peregrine Falcon[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Snow Bunting[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Bald Eagle[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Muskrat[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Rock Ptarmigan[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Gray Jay[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Moose[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Snowy Owl[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Polar Bear Tracks[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Willows [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Flag Tree[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Lemmings[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Tundra Buggy Lodge[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Churchill Sunset[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]Hare Tracks[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Fox Tracks[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]Greywackye Rock[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Hudson Bay[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Fireweed[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Ithaca[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Buggy One[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Kelp/Seaweed[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]Snow[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Grease ice[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]Sun Dogs [/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]No Pants Lake[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]Lunch Point Inukshuk[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]Glacial Esker[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]Fox Den[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the MrExcel board.

Open a new workbook. On Sheet1, put your list, with the numbers in column A, and the names in column B. Add Sheet2. Press Alt-F11 to open the VBA editor. From the menu click Insert > Module. Paste the following code into the sheet that opens:

Sub Bingo()
Dim c As Long, ub As Long, words As Variant, words2 As Variant, i As Long, k As String, MyDict As Object
Dim outcol(), cards As Variant

    c = InputBox("How many cards do you want?")
    Sheets("Sheet2").Cells.ClearContents
    
    If c < 1 Then Exit Sub
    
    words = Sheets("Sheet1").Range("B2:B44").Value
    Set MyDict = CreateObject("Scripting.Dictionary")
    
    While MyDict.Count < c
        words2 = words
        ub = UBound(words)
        k = ""
        For i = 1 To 24
            x = Int(Rnd() * ub + 1)
            k = k & "," & words2(x, 1)
            words2(x, 1) = words2(ub, 1)
            ub = ub - 1
        Next i
        MyDict(k) = 1
    Wend
    ReDim outcol(1 To c, 1 To 1)
    cards = MyDict.keys
    For i = 1 To c
        outcol(i, 1) = Mid(cards(i - 1), 2)
    Next i
    Sheets("Sheet2").Range("A1").Resize(MyDict.Count).Value = outcol
    
    Sheets("Sheet2").Range("A:A").TextToColumns Destination:=Sheets("Sheet2").Range("A1"), _
        DataType:=xlDelimited, Comma:=True
            
End Sub

Press Alt-Q to close the editor. In Excel, press Alt-F8 to open the macro selector, choose Bingo and click Run.

Let me know how it works.

 

[reppert25]

Thanks a lot. I'll try it out when I get in to work tomorrow.

 

[Logit]

.
You mentioned BINGO CARD with center square common.
The download fulfills that description but may not be what you are seeking ?

https://www.amazon.com/clouddrive/share/wIEpKTPtFQdXfk5NBtK72FTJdUQYzAKSYMkSKcV2Y7G

 

[reppert25]

Thanks alot seems to be working perfectly. I went through with a formula and conditional formatting to see if any rows repeated nothing. Can you let me know where in the code it stops it from having repeating rows. I have a fairly good idea what is going on with the code but the not repeating has me stumped.

 

[Logit]

Thanks alot seems to be working perfectly. I went through with a formula and conditional formatting to see if any rows repeated nothing. Can you let me know where in the code it stops it from having repeating rows. I have a fairly good idea what is going on with the code but the not repeating has me stumped.

Me ... or EricW ?

 

[Eric W]

Sure! This pretty much is the entire duplicate key checker:

MyDict(k) = 1

Dictionary objects have some pretty nice features. In that line, you specify the dictionary (MyDict), the key (k), and the value you want to set the item to (1). The key is a string comprised of a random selection of words, calculated in the loop above it. If that line is executed and the key does not already exist in the dictionary, that line will create the key and set the item value. If the key already exists, it sets the value to 1 (again).

But of course, if a duplicate is found, the MyDict.Count value will not increase. That's why I used a "While MyDict.Count < c" loop instead of just a "For i = 1 to c" loop.

Another way to add a key to the dictionary is:

MyDict.Add k,1

If you try to add it this way, you'll get an error if you try to add a duplicate key. So you can check first by using

If MyDict.Exists(k) Then

but that takes some extra code.

Make sense?

 

[reppert25]

Thanks alot. Greatly appreciated and makes perfect sense.