Random Data

[BroncoBryan]

I have a BINGO card with facts about people (not numbers) in each square. I would like to have a formula that would put the facts in each cell randomly so I could have several different versions of the card.

I was thinking like if I put all the facts in L2:L20 (One fact per cell). What formula would I use so that One fact about a person would display in A2, A3, and so on (the range of the card is A2:E12).

I did something similar to this years ago for a Super Bowl board and I think I remember using the Rand function and maybe Index.

Thank you.

 

[Jimmypop]

There might be a shorter way but so far this worked for me...

=CHOOSE(RANDBETWEEN(1,20),$L$2,$L$3,$L$4,$L$5,$L$6,$L$7,$L$8,$L$9,$L$10,$L$11,$L$12,$L$13,$L$14,$L$15,$L$16,$L$17,$L$18,$L$19,$L$20,$L$21)

 

[JGordon11]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Fact16											Fact1
3	Fact4											Fact2
4	Fact7											Fact3
5	Fact18											Fact4
6	Fact8											Fact5
7	Fact20											Fact6
8	Fact3											Fact7
9	Fact17											Fact8
10	Fact15											Fact9
11	Fact14											Fact10
12												Fact11
13												Fact12
14												Fact13
15												Fact14
16												Fact15
17												Fact16
18												Fact17
19												Fact18
20												Fact19
21												Fact20
22
Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=LET(r,RANDARRAY(20),INDEX(L2:L21,MATCH(SMALL(r,SEQUENCE(10)),r,0)))
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B										L
1
2	Fact10											Fact1
3	Fact9											Fact2
4	Fact18											Fact3
5	Fact11											Fact4
6	Fact15											Fact5
7	Fact16											Fact6
8	Fact13											Fact7
9	Fact14											Fact8
10	Fact1											Fact9
11	Fact2											Fact10
12	Fact17											Fact11
13												Fact12
14												Fact13
15												Fact14
16												Fact15
17												Fact16
18												Fact17
19												Fact18
20												Fact19
Data

Cell Formulas
Range	Formula
A2:A12	A2	=INDEX(SORTBY(L2:L20,RANDARRAY(19)),SEQUENCE(11))
Dynamic array formulas.

 

[Jimmypop]

Just wondering how would one do this for the range A2:E12? Thinking there might be a repetion of facts? If not mistaken OP was looking at Range A2:E12?

 

[Fluff]

The formula I supplied can just be copied across.

 

[Jimmypop]

The formula I supplied can just be copied across.

Thanks... This can come in handy for myself as well one time... Guessing would just need to fix the Column and Row reference with $? Like $L$2:$L$20 ?

 

[Fluff]

That's right.

 

[BroncoBryan]

Thank you all for the help. I love how everyone on here is always so willing to help.

 

[Fluff]

Glad we could help & thanks for the feedback.