Creating duplicate cells based on another cell value

[ArtemisJay]

First thread and hoping someone can help.

As part of a reward and recognition incentive at work this month, I’m running a type of raffle. As we work from different sites around Australia, we’re keeping everything digital and recorded using Excel.

I’m wanting to be able to create a vertical list of names and assign random numbers to each then draw randomly from the total for winners.

Is there a way that I can duplicate someone’s name in a spreadsheet multiple times, based on how many tickets into the raffle they’ve been given? I currently have it set as something like Column A is the name of the person and Column B is how many tickets they have.

For example: John has 6 tickets and Jane has 5. I would want the list to be
John
John
John
John
John
John
Jane
Jane
Jane
Jane
Jane

Either an obscure formula or VBA. I’m down for either.

Thanks in advance.

 

[Toadstool]

Hi ArtemisJ,

Can I have a counter column? If so then is this obscure enough?

ArtemisJ.xlsx
	A	B	C	D	E	F
1	Name	Tickets	Counter			Result
2	John	6	6			John
3	Jane	5	11			John
4	Jim	3	14			John
5						John
6						John
7						John
8						Jane
9						Jane
10						Jane
11						Jane
12						Jane
13						Jim
14						Jim
15						Jim
16
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=SUM($B$2:B2)
F2:F16	F2	=IFERROR(INDEX($A$2:$A$11,MATCH(AGGREGATE(15,6,$C$2:$C$11/(ROW()-ROW($F$1)<=$C$2:$C$11),1),$C$2:$C$11,0)),"")

 

[Jon von der Heyden]

Lets assume that the names are in A1:A3, and the number of raffles tickets against each is logged in B1:B3.

Try:

=FILTERXML("<t><s>"&CONCAT(REPT(A1:A3&"</s><s>",B1:B3))&"</s></t>","//s")

 

[Fluff]

With a slight tweak to Jon's idea & random numbers

+Fluff 1.xlsm
	A	B	C	D	E
1	Name	Tickets
2	John	6		John	485
3	Jane	5		John	353
4	Jim	3		John	443
5	Bob	1		John	292
6	Sue	4		John	240
7	Mary	5		John	133
8				Jane	415
9				Jane	188
10				Jane	191
11				Jane	447
12				Jane	225
13				Jim	245
14				Jim	275
15				Jim	376
16				Bob	368
17				Sue	195
18				Sue	461
19				Sue	224
20				Sue	364
21				Mary	186
22				Mary	461
23				Mary	343
24				Mary	332
25				Mary	264
26
Lists

Cell Formulas
Range	Formula
D2:D25	D2	=FILTERXML("<t><s>"&CONCAT(REPT(A2:A7&"</s><s>",B2:B7))&"</s></t>","//s[position()<="&SUM(B2:B7)&"]")
E2:E25	E2	=RANDARRAY(ROWS(D2#),,100,500,1)
Dynamic array formulas.