Is there any way?

[SunnyB01]

Hey All,

After searching for what felt like 73 days, I am hoping someone can help me!!! Here is the situation... I am attempting to mail merge in word from an excel document to print ticket entries for a giveaway, which is simple enough, but here is where it gets tricky - Before I mail merge, I am hoping there is a way to have excel expand the rows of data based of the quantity column. For example, in the table below, I am hoping there is a way to have excel create 6 rows of data for Doug Smith, 12 for Bill Williams, etc as I need to print an individual entry to match the quantity of entries each person has. Or if there is a way to do it via the mail merge in word.

ID	FIRST NAME	LAST NAME	QTY	DESCRIPTION
224	Doug	Smith	6	Prize 1
421	Bill	Williams	12	Prize 2
582	Austin	Davis	2	Prize 3
779	Melissa	Johnson	9	Prize 3

<tbody>
</tbody>

Thank you so much in advance for even looking at this and I am hoping someone out there will be my savior!

 

[rpaulson]

try this on a copy of you file.

It assumes you data is in columns A:E.

it will create a new list in G:K

Sub Macro1()
Range("G:K").ClearContents

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row

sr = Cells(Rows.Count, "K").End(xlUp).Row + 1
lr = sr + Cells(r, "D").Value - 1

Range(Cells(sr, "G"), Cells(lr, "K")) = Range(Cells(r, "A"), Cells(r, "E")).Value

Next r
End Sub

hth,

Ross

 

[SunnyB01]

Ross -

You are my hero! Thank you so much!

Best,
Brooke

try this on a copy of you file.

It assumes you data is in columns A:E.

it will create a new list in G:K

Sub Macro1()
Range("G:K").ClearContents

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row

sr = Cells(Rows.Count, "K").End(xlUp).Row + 1
lr = sr + Cells(r, "D").Value - 1

Range(Cells(sr, "G"), Cells(lr, "K")) = Range(Cells(r, "A"), Cells(r, "E")).Value

Next r
End Sub

hth,

Ross