Solved

Create duplicate entries in Excel with a quantity field

Posted on 2011-02-17
19
971 Views
Last Modified: 2012-05-11
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
Comment
Question by:BaupostJR
  • 10
  • 9
19 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 34919172
Ok - so you want a tab for Large and a tab for Small?

Dave
0
 

Author Comment

by:BaupostJR
ID: 34919200
yes
That way when I go to do the mail merge, I can choose the tab for the size I want
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34919209
ok - give me a couple mins...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34919366
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
 

Author Comment

by:BaupostJR
ID: 34919390
will I be able to edit this to create more fields?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34919432
Absolutely!
0
 

Author Comment

by:BaupostJR
ID: 34919464
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34919561
Here's the solution that allows for creation of additional fields (but last 2 have to be the same)...

Dave
GenerateMailingList-r2.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34919566
You asked if you could - yes, with code changes.  So I made those and now you can do it.

Cheers,

Dave
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:BaupostJR
ID: 34919587
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
 

Author Comment

by:BaupostJR
ID: 34919598
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
 

Author Comment

by:BaupostJR
ID: 34919631
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
 

Author Comment

by:BaupostJR
ID: 34919790
I see why it failed. It doesn't like Null fields
if a field isn't populated, it breaks the macro
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34919833
As long as last 2 colums are as you specified modify as you like

Dave
0
 

Author Comment

by:BaupostJR
ID: 34919849
oh great, I see that now
Thanks a lot.

any way to work around the null field issue?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34919938
Will look
 Upload a sample
0
 

Author Comment

by:BaupostJR
ID: 34919968
I left field M2 empty
that causes the error
GenerateMailingList-r2.xlsm
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 34920786
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
 

Author Comment

by:BaupostJR
ID: 34920817
Perfect! Thank you very much!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now