Weighted lottery

Question

I need to create a weighted lottery where people can have multiple entries and the number of entries is based upon a series of questions. For each question they get right, they get an entry into the lottery.

In my head, I've got a table with the participants' names and their total number of points (entries), then I need some way to have a list of the names with their multiple entries. So if my initial table looked like this:

Name	Points
John	5
Larry	4
Andre	2
Mika	6

Then my output list would look like this:

Name
John
John
John
John
John
Larry
Larry
Larry
Larry
Andre
Andre
Mika
Mika
Mika
Mika
Mika
Mika

Then I could just use the default row leaders from the output table as the entry for the person and use a random number generator to pick numbers.

That being said, I'm open to other ideas.

Answer 1

Try this formula-

=QUERY(INDEX(FLATTEN(SPLIT(REPT(A1:A4&"@",B1:B4),"@"))),"where Col1 is not null")

REPT(A1:A4&"@",B1:B4) will repeat each cell value of A1:A4 as per number in B1:B4. Then Split and flatten with make them a vertical array (column). Query() will return all the values except nulls (if any).

To make it more dynamic you may try-

=QUERY(INDEX(FLATTEN(SPLIT(REPT(A1:INDEX(A1:A,COUNTA(A1:A))&"@",B1:INDEX(B1:B,COUNTA(A1:A))),"@"))),"where Col1 is not null")

And with new LAMBDA() functions.

=FLATTEN(SPLIT(JOIN("",BYROW(A1:INDEX(B1:B,COUNTA(A1:A)),LAMBDA(x,REPT(INDEX(x,1,1)&"|",INDEX(x,1,2))))),"|"))