A couple of years ago, I posted about an Excel bingo workbook, that you could use to create a set of three cards with random numbers.
Bingo Card Formulas
In the Excel workbook, the bingo card sheet uses the INDEX and MATCH functions to pull the numbers from another sheet.
- =INDEX(Numbers!$M$1:$M$15, MATCH(LARGE(
Numbers!$N$1:$N$15, ROW()-1), Numbers!$N$1:$N$15,0))
There were a few follow up workbooks, including an Excel Bingo Number Selector file from Dick Kusleika.
Horizontal Number Lists
This week, Carrie posted a comment on that article, and she wanted to adapt the bingo cards so they could be printed with Adobe InDesign. Instead of a square with 25 numbers, that program needs the 25 numbers in a single row.
Using the example in the screen shot above, the numbers in the first two rows would be arranged like this, followed by the numbers from the remaining rows:
Random Number Code
Jim Cone pitched in, and wrote some code to generate the random numbers in single rows, but it created some duplicates in the rows. Carrie didn’t want an uprising in the bingo hall, so we sent Jim away to try again. 😉
It didn’t take long for him to return with some code that worked correctly, so Carrie, and her Hummel figurine collecting friends, can safely play bingo this summer.
Whew! A bit later, Dick posted an update for Jim’s code. It’s shorter, and does the job very well.
Copy the Random Number Code
Thanks Jim and Dick! I’m sure Carrie appreciates the code, and maybe it will help a few other people.
To use this random number code, copy it to a regular code module in your workbook.
Then, go to a blank sheet, and run the SurelyYouCantBeSerious_R1 code.
Sub SurelyYouCantBeSerious_R1() 'Generates 800 sets of random Bingo numbers with no duplicates. 'Each row contains an individual set of Bingo numbers. 'Designed to be used with the "Adobe InDesign" application. 'Jim Cone - Portland, Oregon USA - February 02, 2011 '[email protected] - remove all "X" 'Edited by Dick dailydoseofexcel.com On Error GoTo DontCallMeShirley Dim arrList(1 To 800, 1 To 25) As Long Dim j As Long Dim R As Long 'B COLUMNS '1 to 15 in the B column Randomize For R = 1 To 800 For j = 1 To 5 FillList arrList, j, R Next j Next R Range("A1:Y800").Value = arrList() Exit Sub DontCallMeShirley: Beep Resume Next End Sub '========== Sub FillList(ByRef arrList As Variant, lStart As Long, R As Long) Dim j As Long Dim C As Long Dim N As Long Dim arrCheck(1 To 75) As Long j = 1 For C = lStart To 25 Step 5 Do While j < 6 'Int((High - Low + 1) * Rnd + Low) N = Int(Rnd * 15 + ((lStart - 1) * 15 + 1)) If arrCheck(N) < 1 Then arrList(R, C) = N arrCheck(N) = N j = j + 1 Exit Do End If Loop Next C End Sub
______________
I have a comment and a question…
1) The comment section in the posted code says “‘Generates 800 sets…”, but the loops only iterate 80 times generating what, in reading the code, looks like only 80 sets. So, is that a typo in the comment section?
2) I’m a little confused at what a set of “numbers” should look like. I see sets of 25 numbers are being generated, but a Bingo card actually only has 24 numbers on it… the middle space (corresponding to the 13th number in the set) is always the word “FREE”. So, does Carrie’s Adobe InDesign program take 25 numbers and automatically replace the 13th number with the word FREE when it runs? Or does it actually require 24 numbers be given to it in generated column sets of 5, 5, 4, 5, 5 numbers so that it will flow these around the middle space automatically? Or should each set automatically have the 13th number replaced by the word “FREE” before being given to Carrie’s Adobe InDesign program?
Thanks Rick!
1) I changed the code to 80, while testing, and forgot to change it back to 800. It’s fixed now.
2) Not sure — Maybe Carrie will manually change the middle number to “FREE”, or InDesign can be programmed to do that.
@Deb,
The big problem comes if InDesign is set up to skip over the “FREE” space automatically… in that case only 24 numbers should be delivered or wrong numbers could leak into the adjacent trailing columns. Although in thinking about it, I guess Carrie could just delete the 13th column and that should work out correctly.
Thanks so much for this code! It saved us on a big job here. And it worked like a charm!
I set up the Bingo portion of the card in Indesign as a table with 25 cells – the middle one just with the word FREE, but the active 24 cells each as a VDP placeholder. When you link up 24 columns with those 24 fields, the Data Merge creates 800 perfect, random BINGO cards.
Thanks again!
John,
Can you please upload the indesign file and excel format? I want to create bingo cards for my students and use pictures. I am using 25 boxes and NO BINGO just calling out a definition or showing a picture. Please help! We are doing this for a end of the year review and I am stuck!
Andrew
Andrew – I’ll be glad to give you the InDesign file I created and the Excel file that was generated by the code supplied here. The only problem is I can’t upload them to this website just via the comment thread. If you’d like to reply here with your email address (I know that makes it public but don’t know another way) – I’ll be glad to send to you what I have. Also, please tell me a couple of things:
1- Are working on a Mac or PC?
2 – What version of InDesign you are using so I can send you compatible files.
Also, not clear on how you are going to be using pictures with your BINGO cards…the files I have will just supply random numbers in the cards 24 boxes.
John
Hello!
Thank you for the prompt reply! I am so sorry I didn’t respond earlier, I was on vacation. My email address is (removed). I have a Mac. I am using using indesign CS6 Version 8 from the Adobe Masters collection. Currently, I have 49 vocabulary words that I want incorporate on my bingo boards.
They are warm colors, cool colors, color wheel, neutral colors, complementary colors, intermediate colors, analogous colors, primary colors, secondary colors, easel, curly line, curved line, diagonal line, horizontal line, parallel lines, spiral, vertical line, wavy line, dashed line, zigzag line, geometric shapes, organic shapes, Geometric figures, pottery, portrait, pottery, sculpture, landscape, value scale, tint, texture, positive space, negative space, overlap, one point perspective, contrast, palette, green, orange, violet, still life, dotted line, pencil, paint brush, line quality, composition, cube, square, shade, tone.
I have been trying to make these boards for my 5th graders and use data merge in indesign. I have absolutely no idea how to do this. I am NOT calling letters and number just vocabulary words to see if them have the term on their card.
Are you able to help me. I know my kids and I would really appreciate it!
Andrew
This worked AWESOME! I changed the code to generate 10,000 cards. You can make it generate as many or as few as you need. The only altering you have to do afterward is to create headers for the different columns. Mine are B1, B2, B3, B4, B5, I1, I2, etc… As for the free space, I just skipped N3. Also, I created a table in InDesign for the merge.
Thanks, Wesley, and that’s a lot of bingo cards!