Microsoft Word/Tough Mail Merge Question
QUESTION: Hi Aidan:
I use Microsoft 2007 and sometimes 2010 (Windows 7).
Please tell me how to do an advanced mail merge (data from Excel and merge into a Word letter template) that allows me to also pull data from the same column. Although I know how to do a simple mail merge where I pull data from the same row and merge into one letter for each record (e.g. 100 rows of record from Excel = 100 letters in Word). But now, I need to merge into one letter going to the same law firm (addressee is the law firm) but with different client names in the body of the letter. Specifically, the column headings in Excel are: File #, Client Name, Lawyer Name, and Law firm Address. However, there are sometimes as many as 18 clients who are with the same law firm. That is, a different client is listed in each of the 18 rows under the Client Name column in Excel and the same law firm is on all these rows. How can I merge various client names into the same letter that goes to the same law firm?
ANSWER: There are (I think) two methods of doing this - Microsoft suggest using a directory style merge - http://support.microsoft.com/kb/294686
- my method is to use a UDF in Excel to amalgamate the data prior to merge - though this does require macros - see http://en.allexperts.com/q/Excel-1059/2009/11/Concatenate-Text-String-multiple.h
for the answer to a similar question with a link to a file - in this instance, you would filter your merge so it only merged records where the count field was 1 - I have a decreasing COUNTIF function in place here.
---------- FOLLOW-UP ----------
QUESTION: Thanks so much, Aidan, but I am still stuck. I read and tried your previous answer:
"I would do this using a User Defined Function in VBA -=- seehttp://www.aidanheritage.byethost3.com/excel/MergeExample.xls
for an example of this. The function is stored in a module on the workbook - copy this module to any other workbook to use it (alt F11 to get into the editor, then copy). Any problems, let me know."
I have only intermediate-level Microsoft Excel and Word skills. Is it possible to provide me with step-by-step instructions?
I'm not 100% sure where you are stuck - but I'll try
Open my sample file
Open your file
Press Alt F11 to get into the VBA Editor
Use the project explorer on the left hand side (if not visible, press Ctrl R) to copy the module in my sample file to your file - you can do this easiest by dragging and dropping - the default action for this is copy.
This will give you a new function which you can use in your workbook.
Save your workbook
close my sample file as it is no longer needed.
Use my GetGroup function wherever you need it - the function works like this
where A2 is the value you want to test, A1:A7 is the full range of cells to check and 1 is the number of cells to the right you wish to offset by (so in this instance the data stored would come from column B as that is one to the right of column A)
In another helper column use a function like
where A2:A7 represent the full range of cells to be counted - note the mixed use of dollar signs - this is important as when dragged down the range will slowly decrease (so in C3 the formula becomes =COUNTIF(A3:$A$7,A3) - which gives you a decreasing count)
You can now use these two new fields in your mail merge - the countif function will be used to filter the data source to only merge on records containing a 1 - so you get one document per client. The getgroup column will be used for the data field.
I hope this has helped, but if not do let me know - if it helps (eg for sending example files) my email is email@example.com
About Microsoft Word
This topic answers questions related to Microsoft Word stand-alone or Microsoft Office Word including Word 2003, Word 2007, Office 2000, and Office XP. You can get Word help on formatting text, tables, tabs, fonts, styles, general Word layouts, bullets, headings, and outlines, using templates, toolbar modifications, and using Track Changes. You may also find tips on linking Word and Excel embedded objects including charts. This site does not provide a general Word tutorial nor the basics of using a word processor. It provides specific answers to using Microsoft Word only. If you do not see your Word question answered in this area then please ask a Word question here