Solved

Create duplicate entries in Excel with a quantity field

Posted on 2011-02-17
19
977 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 42

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 42

Expert Comment

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

Dave
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 42

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 42

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 42

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 42

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
 

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 42

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 42

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 42

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

690 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