Search code examples
excelrandomexcel-formula

How Can I Generate Randomized Text-Based Bingo Cards with Unique Columns

For a variety of reasons, I'm trying to develop

  • A bingo card
  • of text options
  • from a table of 5 columns and n rows (named Options)
  • with each column only pulling from a single column
  • with unique answers from each
  • without adding a column of numbers using rand()
  • using Excel 365
  • that doesn't require a rand() column

Example: Party Bingo, where columns are categories of things having to do with Music, Food & Drink, Games, People, Misc. Example Data | Example Resulting Card

I want B to only pull from Music, and I to only pull from Food & Drink, etc.

I would like this to be dynamic, because the columns don't all have the same number of entries. So, I was trying to account for empty cells, which gave me this - unfortunately it doesn't spill so I don't get unique answers

=LET(filt,FILTER(Options,Options[Music]<>""),(INDEX(filt,RANDBETWEEN(1,ROWS(filt)),1)))

I'm starting to think there's no way I can do 1 function and get a 5x5 array like this, so now I'm trying to figure how to do a different function for each of the B I N G O columns that spills down so I can get unique options for each.

I tried this but I definitely messed something up here because it returns '#REF!' most of the time

=LET(filt,FILTER(Options,Options[Food & Drink]<>""),INDEX(filt,SEQUENCE(5),2,RANDARRAY(5,1,1,COUNTA(filt))))

I feel like I'm on the right track but I'm hitting a wall. Any advice is appreciated!

Solution

  • You can use this formula:

    =LET(d,Options,cntRows,5,
cntCol,COLUMNS(d),
λCleanData,LAMBDA(d,cntMin,TAKE(d,MAX(cntMin,COUNTA(d)))),
λRandomSort,LAMBDA(x,cntR,SUBSTITUTE(TAKE(SORTBY(x,RANDARRAY(ROWS(x))),cntR),0,"-")),
DROP(REDUCE("",SEQUENCE(1,cntCol),LAMBDA(r,c,
                         HSTACK(r,λRandomSort(λCleanData(INDEX(Options,,c),cntRows),cntRows)))),0,1))

    enter image description here

    λCleanData returns the values of one column - based on the minimum number of rows - if there are empty rows. This is necessary as otherwise too many empty cells might be returned by the random sorting.

    λRandomSort does the random sorting on a column.

    REDUCE together with HSTACK can handle unequal column sizes. I tried BYCOL together with the λ-function but that returns errors.

    UPDATE to not replace 0 within card

    =LET(d,Options,cntRows,5,
cntCol,COLUMNS(d),
λCleanData,LAMBDA(d,cntMin,MAP(TAKE(d,MAX(cntMin,COUNTA(d))),LAMBDA(a,IF(a=0,"-",a)))),
λSort,LAMBDA(x,cntR,TAKE(SORTBY(x,RANDARRAY(ROWS(x))),cntR)),
DROP(REDUCE("",SEQUENCE(1,cntCol),LAMBDA(r,c,
                         HSTACK(r,λSort(λCleanData(INDEX(Options,,c),cntRows),cntRows)))),0,1))

    λCleanData now returns a - instead of 0 in case of an empty cell.