Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

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...
0
BaupostJR
Asked:
BaupostJR
  • 10
  • 9
1 Solution
 
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
 
dlmilleCommented:
ok - give me a couple mins...

Dave
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
dlmilleCommented:
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
 
BaupostJRAuthor Commented:
Perfect! Thank you very much!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now