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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[TD]QTY[/TD]
[TD]DESCRIPTION[/TD]
[/TR]
[TR]
[TD]224[/TD]
[TD]Doug[/TD]
[TD]Smith[/TD]
[TD]6[/TD]
[TD]Prize 1[/TD]
[/TR]
[TR]
[TD]421[/TD]
[TD]Bill[/TD]
[TD]Williams[/TD]
[TD]12[/TD]
[TD]Prize 2[/TD]
[/TR]
[TR]
[TD]582[/TD]
[TD]Austin[/TD]
[TD]Davis[/TD]
[TD]2[/TD]
[TD]Prize 3[/TD]
[/TR]
[TR]
[TD]779[/TD]
[TD]Melissa[/TD]
[TD]Johnson[/TD]
[TD]9[/TD]
[TD]Prize 3[/TD]
[/TR]
</tbody>[/TABLE]


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