Is there any way?

SunnyB01

New Member
Joined
May 9, 2017
Messages
2
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.

IDFIRST NAMELAST NAMEQTYDESCRIPTION
224DougSmith6Prize 1
421BillWilliams12Prize 2
582AustinDavis2Prize 3
779MelissaJohnson9Prize 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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,100
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

Code:
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

New Member
Joined
May 9, 2017
Messages
2
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,756
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top