SUMMARY
This article describes how to use the Mail
Merge feature in Word to create a list of data that is sorted and separated by
a category. This article contains instructions and a sample that you can use to
create such a list.
Set up the data file
Sort your data file, so that all records with the same value for
the key field category (the field upon which you base the sort) appear
together, as shown in the sample data file. The following sample list is sorted
by the CITY field (CITY is the key field in this example).
City |
Employee |
Sales |
Atlanta |
Galos |
$3,000 |
Atlanta |
Delaney |
$50,000 |
Atlanta |
Henningsen |
$10,000 |
Houston |
Johnson |
$8,000 |
Houston |
Kelly |
$9,000 |
Houston |
Pak |
$0 |
Set up the main document
Note
¶ designates
The words "a paragraph mark" in brackets designate a paragraph mark
in the following example. To type a paragraph mark, press ENTER.
For
this procedure to work, you must select the "Directory" document type when you
set up your main document. To set up your main document as a directory, follow
these steps:
- In a new blank document, start the mail merge.
Word 2002
On the Tools menu, click Letters and Mailings, and then click Mail Merge Wizard.
Word 2003
On the Tools menu, click Letters and Mailings, and then click Mail Merge.
- In the Mail Merge task pane under Select document type, click to
select Directory.
- Click Next: Starting document.
- Under Select starting document, click to
select Use the current document.
- Click Next: Select recipients.
- Under Use an existing list, click Browse.
- In the Select Data Source dialog box, select the sample file you created in the
Set Up the Data File section.
- If the Confirm Data Source dialog box appears, select a data type that is appropriate for
the type of file that you created in the Set up
the data file section. For example, if you created
the file in Microsoft Excel, select either MS Excel Worksheets via DDE
(*.xls) or Excel Files via ODBC (*.xls).
- In the Mail Merge Recipients dialog box, click OK.
- Using the following example as a guide, insert fields to
compare the key field in each row with the key field in the previous row, and
to insert one string of text if they are different and another string of text
if they are the same.
Example This example uses the sample data from the
Set up the data file section, and it uses
{ MERGEFIELD CITY
} as the key field.
Note To insert the field braces, press CTRL+F9.
{ IF { MERGESEQ } = "1" "{ MERGEFIELD CITY }¶
" "" }{ SET Place1 { MERGEFIELD CITY }}¶
{ If { Place2 } <> { Place1 }"¶
{ MERGEFIELD CITY }¶
¶
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶
The fields laid out in this example produce a
sorted list as follows:
Atlanta
Galos $3,000
Delaney $50,000
Henningsen $10,000
Houston
Johnson $8,000
Kelly $9,000
Pak $0
Force each new category to a new page
As in the previous example, the key field in this example is
{ MERGEFIELD City
}. When the value of CITY changes in the data file to a different
city, a new page is added to the merged results, and the merge is continued at
the top of the next page. To insert the field braces, press CTRL+F9.
{ If { MERGESEQ } = "1" "{ MERGEFIELD CITY }¶
" ""}{ SET Place1 { MERGEFIELD CITY }}¶
{ If { Place2 } <> { Place1 }"¶
----------------------------Page Break--------------------------------
{ MERGEFIELD CITY }¶
¶
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶
Note To insert a page break, either press CTRL+ENTER, or click
Break on the
Insert menu, select
Page Break, and then click
OK.
The fields laid out in this example produce a listing
on separate pages as follows:
Atlanta
Galos $3,000
Delaney $50,000
Henningsen $10,000
----------------------------Page Break-----------------------------
Houston
Johnson $8,000
Kelly $9,000
Pak $0
Format the key field
The key field in this example is
{MERGEFIELD CITY
}. To format the results of the
{MERGEFIELD CITY
} as all capital letters, you can insert the formatting switch of
\* Upper. To insert the field braces, press CTRL+F9.
{ If { MERGESEQ } = "1" "{ MERGEFIELD CITY \* Upper }¶
" "" }{ SET Place1 { MERGEFIELD CITY }}¶
{ IF { Place2 } <> { Place1 }"¶
{ MERGEFIELD CITY \* Upper }¶
¶
{ MERGEFIELD EMPLOYEE }{ MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE }{ MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶
The fields laid out in this example produce a listing with the
city appearing in all uppercase letters, as follows:
ATLANTA
Galos $3,000
Delaney $50,000
Henningsen $10,000
HOUSTON
Johnson $8,000
Kelly $9,000
Pak $0
Note You can apply different formatting to the key field
{ MERGEFIELD City
} by selecting the whole field (including the field braces
{ }), and formatting the field to your preferences. To format the
field, click
Font on the
Format menu.
Include additional text
If you want additional text to appear in the merge document, you
must include this text inside the sets of quotation marks in the IF fields.
This way, the text appears only if the information in the data source meets the
conditions that you define in the IF fields (for example, when the CITY name in
the current row of the data source differs from the CITY name in the previous
row).
If you place text outside the IF fields in your main document,
this text appears in the merge document one time for each new row in the data
source, rather than one time for each new key field. In the example that you
created in the
Set up the data file
section, it appears one time for each employee, instead of one time for each
city.
This example introduces each city in the list with a short
introductory sentence:
{ IF { MERGESEQ } = "1" "These are the sales totals for { MERGEFIELD CITY }:¶
" "" }{ SET Place1 { MERGEFIELD CITY }}¶
{ If { Place2 } <> { Place1 }"¶
These are the sales totals for { MERGEFIELD CITY }:¶
¶
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶
The fields laid out in this example produce a listing with
introductory text for each city as follows:
These are the sales totals for Atlanta:
Galos $3,000
Delaney $50,000
Henningsen $10,000
These are the sales totals for Houston:
Johnson $8,000
Kelly $9,000
Pak $0
How this process works
The first IF field in these examples inserts the city name and a
paragraph mark for only the first record in the mail merge. For all subsequent
records, this IF field inserts nothing (""). It recognizes the first mail-merge
record by comparing the MERGESEQ field (which returns the sequence number for
the current record) to the numeral "1."
The second IF field inserts
a return character (and a page break if you are forcing each city to a new
page), the city name, two more return characters, the employee name, and the
sales amount (in that order), only if the IF field determines that the current
record includes a city name that is different from the previous record. If Word
determines that the current record includes a city name that is the same as the
previous record, Word inserts only the next employee name and sales amount.
This IF field recognizes the new city name by comparing the text of
the bookmarks specified in the two SET fields. The text of the bookmark
"Place1" is always equal to the current record's City field, and the text of
the bookmark "Place2" is equal to the previous record's City
field.