Create duplicate entries in Excel with a quantity field

I want to create an excel document with a bunch of address entries for a mail merge
I need to be able to have a master list of all the addresses on one sheet. I want to have a field on that sheet for 2 types of quantities: large labels and small labels
Once you put in the quantity of large and small labels, I wanted it to create a sheet for the small labels and a sheet for the large labels based on the quantity

IE, I have a field that says
Name; Address; City, State; ZIP; Large label; Small label
John Doe; 1 Address St; Anywhere MA; 12345; 5; 5
Jane Doe; 2 Address St; Anywhere MA; 12345; 2; 3

then on the Large Label sheet you'd have
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
Jane Doe; 2 Address St; Anywhere MA; 12345
Jane Doe; 2 Address St; Anywhere MA; 12345

and on the small sheet I'd have

John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
John Doe; 1 Address St; Anywhere MA; 12345
Jane Doe; 2 Address St; Anywhere MA; 12345
Jane Doe; 2 Address St; Anywhere MA; 12345
Jane Doe; 2 Address St; Anywhere MA; 12345

So, once I created a mail merge for the labels in Word, it would create 5 duplicate large and small labels for John Doe, 2 Large Jane Doe and 3 Small Jane Doe labels.

Unless there's an easier way to do this...
BaupostJRAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dlmilleConnect With a Mentor Commented:
Had to get back from lunch :)

Ok - as long as your headers have no blank cells, as I don't account for that, you should be good to go.


Dave
GenerateMailingList-r3.xlsm
0
 
dlmilleCommented:
Ok - so you want a tab for Large and a tab for Small?

Dave
0
 
BaupostJRAuthor Commented:
yes
That way when I go to do the mail merge, I can choose the tab for the size I want
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
dlmilleCommented:
ok - give me a couple mins...

Dave
0
 
dlmilleCommented:
Ok - here's the result.  I have a tab called "MailingAddresses" which hosts your initial dataset and has a command button to run the macro.

The macro deletes "past" results from Large and Small tab before it starts.

The macro traverses from 2nd row to end of the range (assumes there are NO blanks in column A before it gets to the end) - hence, the range.end(xldown) form I use to get that.

Then it processes, one row at a time, looking at Large # and Small # - it loops thru 1 to Large# generate the Large, and Small# to generate the Small.  After generating one row, it increments the cursor rLargeCursor and/or rSmallCursor to keep track of where the last output was set, and to position for next output.

Here's the code:
Sub GenerateMailingLargeAndSmall()
Dim myCell As Range, iLargeCount As Integer, iSmallCount As Integer, iLg As Integer, iSm As Integer
Dim rLargeCursor As Range, rSmallCursor As Range

    Sheets("Large").Range("A2", Range("A2").End(xlDown).Address).EntireRow.Delete
    Sheets("Small").Range("A2", Range("A2").End(xlDown).Address).EntireRow.Delete
    Set rLargeCursor = Sheets("Large").Range("A2")
    Set rSmallCursor = Sheets("Small").Range("A2")
    
    Sheets("MailingAddresses").Activate
    
    For Each myCell In Range("A2", Range("A2").End(xlDown))
        iLargeCount = myCell.Offset(0, 4).Value
        iSmallCount = myCell.Offset(0, 5).Value
        
        For iLg = 1 To iLargeCount
            Range(rLargeCursor, rLargeCursor.Offset(0, 3)).Value = Range(myCell, myCell.Offset(0, 3)).Value
            Set rLargeCursor = rLargeCursor.Offset(1, 0)
        Next iLg
        For iSm = 1 To iSmallCount
            Range(rSmallCursor, rSmallCursor.Offset(0, 3)).Value = Range(myCell, myCell.Offset(0, 3)).Value
            Set rSmallCursor = rSmallCursor.Offset(1, 0)
        Next iSm
    Next myCell
End Sub

Open in new window


And see attached file.

Enjoy!

Dave
GenerateMailingList-r1.xlsm
0
 
BaupostJRAuthor Commented:
will I be able to edit this to create more fields?
0
 
dlmilleCommented:
Absolutely!
0
 
BaupostJRAuthor Commented:
When I go to split City and State into separate fields, it moves the ZIP over one to the right and it thinks the ZIP is the quantity...
what am I missing here?
0
 
dlmilleCommented:
Here's the solution that allows for creation of additional fields (but last 2 have to be the same)...

Dave
GenerateMailingList-r2.xlsm
0
 
dlmilleCommented:
You asked if you could - yes, with code changes.  So I made those and now you can do it.

Cheers,

Dave
0
 
BaupostJRAuthor Commented:
So, if I need to add additional fields, what in the code determines the location of the quantity field?

Thanks for all the help. This makes this project a lot easier
0
 
BaupostJRAuthor Commented:
when I run that latest one, I get a runtime error 13 type mismatch

this is highlighted in the debugger:
 iLargeCount = myCell.End(xlToRight).Offset(0, -1).Value
0
 
BaupostJRAuthor Commented:
Like, let's just say I need up to 25 fields before the quantity field... that way I don't run into any coding issues later

Thanks
0
 
BaupostJRAuthor Commented:
I see why it failed. It doesn't like Null fields
if a field isn't populated, it breaks the macro
0
 
dlmilleCommented:
As long as last 2 colums are as you specified modify as you like

Dave
0
 
BaupostJRAuthor Commented:
oh great, I see that now
Thanks a lot.

any way to work around the null field issue?
0
 
dlmilleCommented:
Will look
 Upload a sample
0
 
BaupostJRAuthor Commented:
I left field M2 empty
that causes the error
GenerateMailingList-r2.xlsm
0
 
BaupostJRAuthor Commented:
Perfect! Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.