Help needed translating number to rows for drawing

[rlleblan1]

I have an excel sheet with names and number of "tickets" they have earned. The goal is to use a random number generator to select multiple winners for a drawing. For this to work, I need a way of taking a large spreadsheet and creating a new row with the name of the individual based on the number of tickets. For example, Cell A2 has the name john and cell B2 has "60", which is the number of tickets he earned. In cell A3 is the name Mary and Cell B3 has "20".

The output, on a new worksheet, would generate 60 rows with the name John in it followed by 20 rows with the name Jane.

Attached an example. Thanks in advance for your help and insight.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rlleblan1]

updated information. Uploading new image of actual report. little more complicated

 

[rlleblan1]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

365. I also updated my account details as suggested.

 

[Fluff]

It can be done with the layou you 1st showed, but not the 2nd layout. You should avoid using merged cells, as the are an abomination & always cause problems.

 

[rlleblan1]

It can be done with the layou you 1st showed, but not the 2nd layout. You should avoid using merged cells, as the are an abomination & always cause problems.

afraid that was the case.

 

[Fluff]

With the 1st layout you could do it like

+Fluff 1.xlsm
	A	B	C	D
1
2	John	2		John
3	Mary	1		John
4	Peter	5		Mary
5	Sue	7		Peter
6				Peter
7				Peter
8				Peter
9				Peter
10				Sue
11				Sue
12				Sue
13				Sue
14				Sue
15				Sue
16				Sue
17
Lists

Cell Formulas
Range	Formula
D2:D16	D2	=FILTERXML("<k><m>"&MID(CONCAT(REPT("</m><m>"&A2:A5,B2:B5)),8,33000)&"</m></k>","//m")
Dynamic array formulas.

 

[rlleblan1]

added image of data without combined fields.

 

[Fluff]

In that case see post#7

 

[rlleblan1]

In that case see post#7

it works when I put in the field! how do I account for an infinitie number of names in row "A" resulting in who knows how many rows based on ticket amount.. The current sheet has 583 rows and a total of almost 1000 tickets.