Dynamically extend list help

[tezza]

Hi,

I'm in the process of setting up a raffle generator and prize winner.

Step 1 : Enter Name and how many tickets they've bought

Step 2 : Create a dynamic list of names and tickets numbers depending on how many tickets are purchased per person

Step 3 : Allow someone to hit a button to generate the next winner without picking a duplicate number

It works, but I'm oddly looking struggling with making Step 2 dynamic.

Currently the formula I come across is:

=LET(x,FILTER(F2:F19,COUNTIF(H2:INDEX(H:H,COUNTA(H:H)),F2:F19)=0),INDEX(x,RANDBETWEEN(1,ROWS(x))))

The list is fixed at reading F2:F19.

How can I change it so F19 updates to include the full list of names? If I extend past the list range into blanks, then at some point, it will pick a blank due the the cell being empty.

Here's a visual of where I'm at (no frills until I can get this last bit sorted.

Col J will have a button in there to run the next winners code.

RAFFLE TICKET GENERATOR.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	NAME	SPEND	TICKETS		LIST	TICKET NUMBER		RAFFLE	WINNER		NUMBER GENERATOR
2	Alberto Holcomb	£1.00	1		Alberto Holcomb	0001		0018	Rosalinda Ruffin		12
3	Obed Rosales	£2.00	2		Obed Rosales	0002		0011	Jim Fontenot
4	Roxanna Whatley	£1.00	1		Obed Rosales	0003		0005	Kinley Livingston
5	Kinley Livingston	£3.00	3		Roxanna Whatley	0004		0009	Carlos Goss
6	Carlos Goss	£2.00	2		Kinley Livingston	0005		0003	Obed Rosales
7	Jim Fontenot	£3.00	3		Kinley Livingston	0006		0014	Juancarlos Zeller		5000
8	Juancarlos Zeller	£4.00	4		Kinley Livingston	0007		0006	Kinley Livingston
9	Rosalinda Ruffin	£2.00	2		Carlos Goss	0008		0001	Alberto Holcomb
10					Carlos Goss	0009		0002	Obed Rosales
11					Jim Fontenot	0010
12					Jim Fontenot	0011
13					Jim Fontenot	0012
14					Juancarlos Zeller	0013
15					Juancarlos Zeller	0014
16					Juancarlos Zeller	0015
17					Juancarlos Zeller	0016
18					Rosalinda Ruffin	0017
19					Rosalinda Ruffin	0018
20
21
22
Sheet1

Cell Formulas
Range	Formula
E2:E19	E2	=TOCOL(IFS(C2:C30>=SEQUENCE(,MAX(C2:C30)),A2:A30),2)
F2:F19	F2	=SEQUENCE(COUNTA(E:E)-1,1)
K2	K2	=LET(x,FILTER(F2:F19,COUNTIF(H2:INDEX(H:H,COUNTA(H:H)),F2:F19)=0),INDEX(x,RANDBETWEEN(1,ROWS(x))))
B2:B9	B2	=C2*1
I2:I22	I2	=IFERROR(IF(H2="","",XLOOKUP($H2,$F:$F,$E:$E)),"")
Dynamic array formulas.

Thank you.

 

[Cubist]

Can't you use spill range reference i.e. replace F2:F19 with F2#?

 

[tezza]

Can't you use spill range reference i.e. replace F2:F19 with F2#?

I've never used that before and it works like a dream.

Thank you.

 

[Cubist]

You're welcome. You can read more about it here.

Spilled range operator - Microsoft Support

Learn how to use the spilled range operator to return a spilled array range in its entirety.

support.microsoft.com