All examples in this document use the source.xls database as the source of the data. The mailing label document is merge.doc. You should use the appropriate database source for your needs. The name of the important filtering field in source.xls is STATUS, your field name may be different. We will also use a temporary database named temp.xls to hold the filtered data needed by MS Word.
Often a person may fall into more than one category. When a person fits into more than one category, enter each code into STATUS. The codes may be in any order but with no spaces or commas separating them. For example a person who is a private donor and a volunteer has a STATUS of pv or vp. Using the STATUS field this way allows for the most flexibility, extensibility, and power.
|Exactly the same||= v||v and only v|
Not equal to
|Exactly different||<> v||everything but v, including blanks|
|For numbers, anything less than. For letters, in dictionary order A is less than B.||< p||c and e|
|For numbers, anything greater than. For letters, in dictionary order B is greater than A.||>e||p and v|
Less than or equal
|Combines the less than and the equal to comparisons.||<= p||c, e, and p|
Greater than or equal
|Combines the greater than and the equal to comparisons.||>= e||e, p, and v|
Wildcards are a very important and powerful tool in filters. You may think of a wildcard as a placeholder for one or many numbers or letters. The two important wildcards are the asterisk (*), and the question mark (?). Review the following examples for greater understanding.
The * wildcard is a placeholder for any number of digits or letters.
The results, what will print, are the filled in portions in Figures 2 and 3. Figure 2 shows the result of A OR B. Any person who likes apples (A), bananas (B), or apples and bananas (AB) will be selected using OR. Figure 3 shows the result of A AND B. Only persons who like both apples and bananas (AB) will print. If there are no persons with both A and B, no records will print.
Now, let's pull this together and do some example filters. We'll start easy and proceed to more difficult ones.
Now, write down what records you want to print and convert it to a filter using the comparison operators, wildcards, and joins. Now that you have the filter you need to put it into MS Excel.
You may have both files open at the same time. Switching between the open files is easy. Each open file is called a window, so all you need to do is switch between the windows. To switch windows, from the menu select Window >> temp.xls or Window >> source.xls. The last items in that menu indicate open windows you may switch to. See Figure 6.
The filter you have chosen is applied to the source.xls database in MS Excel. My source.xls contains the sample database shown in Table 3. This is the data all the example filters are performed on. The field names are the titles of the columns; such as LAST, CITY, STATE, and STATUS. The first 10 rows of source.xls should be empty. If necessary insert some blank rows.
The filter should contain at least one of the field names. Copy the field names used in your filter and paste them into the first row of source.xls. The copied names should be exactly the same as the field names used in your database. For example if your filter is STATUS = *p*, then the required field name is STATUS. If your filter is ((STATUS = *p*) AND (CITY = Milwaukee)) OR (STATUS = *e*) OR ((STATUS = *v*) AND (STATE = Wisconsin)), then the required field names are STATUS, CITY, and STATE (see Figure 7). If you include unused field names in your filter criteria then the applied filter will not give the expected results.
Now, enter your filter into the field columns. If the comparison is =, don't include the =. For joins, ANDs go on the same row while ORs go on separate rows. See the following tables for examples of various filters.
|Table 4: Find anyone who is a private donor. The filter is STATUS = *p*.||Table 5: Find everyone who has no STATUS. The filter is STATUS <> *.||Table 6: Find everyone who is a volunteer or an employee. The filter is (STATUS = *v*) OR (STATUS = *e*).|
|Table 7: Find only people who are volunteers and private donors. The filter is (STATUS = *v*) AND (STATUS = *p*).||Table 8: Find private donors who live in Milwaukee. The filter is (STATUS = *p*) AND (CITY = Milwaukee).|
|Table 9: Find private donors who live in Milwaukee, all employees or volunteers who live in Wisconsin. The filter is ((STATUS = *p*) AND (CITY = Milwaukee)) OR (STATUS = *e*) OR ((STATUS = *v*) AND (STATE = Wisconsin))|
|Table 10 : Find persons with no STATUS living in Wisconsin, and volunteers who are also private donors living in zip codes from 50000 to 60000. The filter is ((STATUS<>*) AND (STATE = Wisconsin)) OR ((STATUS = *v*) AND (STATUS = *p*) AND (ZIP >= 50000) AND (ZIP <=60000)).|
Before performing the filter, make sure you have already entered your filter criteria in the first few rows of the spreadsheet. Next, select the first field name in your database as shown in Figure 8.
For illustration, the following figures apply the (STATUS = *v*) AND (STATUS = *p*) filter to the sample data from Table 3.
Apply the filter by choosing Data >> Filter >> Advanced Filter as shown in Figure 9. You may easily remove the filter by choosing Data >> Filter >> Show All.
The Advanced Filter dialog box should appear, see Figure 10. The Action should be "Filter the list, in-place". The List range is your database and it should already be properly selected. The Criteria range is the only thing you should have to provide. Click on the little white, black, and red box to the right of criteria range, this is shown in Figure 10 by the arrow. If you accidentally click on the List range, return to the dialog box by hitting Enter.
After you click the box, your screen should look similar to Figure 11. Use your mouse to select the filter you entered in the first few rows of the spreadsheet. When the filter is selected, hit Enter. Then the Advanced Filter dialog box, shown in Figure 10, should appear again. Click OK.
You have just performed your filter, congratulations. The number of records shown on the screen should have changed and the row numbers of the filtered records should be in blue.
As an example see Figure 12 for the results of the filter (STATUS = *v*) AND (STATUS = *p*). The next step is getting the data ready for MS Word.
Select the filter results by using the shortcut CTRL+SHIFT+*. This shortcut may be used by clicking in the first field name in your database. You Selected the first field earlier as shown in Figure 8. Next, hold down the CTRL key and the SHIFT key and then tap the * key (above the number 8). When your filter results are selected they are highlighted in black. Now, copy your filter results to the clipboard using Edit >> Copy. See Figure 13.
The contents of the clipboard now need to be pasted into temp.xls. Switch to temp.xls. Switching between windows was discussed earlier. Select the entire spreadsheet by clicking on the intersection of the row numbers and the column letters, this location is indicated by the arrow in Figure 13. Delete the contents of temp.xls by hitting the delete key. Next, click in the first cell, A1, of the spreadsheet. Paste the filter results from the clipboard into temp.xls by choosing Edit >> Paste. The results of your filter are now safely in temp.xls.
Exit Excel by choosing File >> Exit or clicking on the X box in the upper right of the window, see Figure 14. Remember, it's okay to save temp.xls but not source.xls.
Start your merge document in MS Word. Open the file merge.doc the way you normally would. You may also start Word and then use File >> Open to get a Open dialog box. Opening files in Word is done the same as in Excel. Once the document is loaded in Word you are ready to start a mail merge. The same merge.doc may be used for all of your databases.
Choosing Tools >> Mail Merge opens the Mail Merge Helper dialog box shown in Figure 16. Most of the information required to perform the merge is saved with your document, so we may ignore most of the buttons. Your Mail Merge Helper dialog box should look similar to Figure 16.
In section 1, Main document, of the dialog box the Merge type should be 'Mailing Labels' and the Main document should be '??????\merge.doc'. In section 2, Data source, the Data should be 'temp.xls!Entire Spreadsheet'. For section 3, Merge the data with the document, the Options in effect should be 'Suppress Blank Lines in Addresses' and 'Merge to new document'.
If the Data source in section 2 is incorrect see Setting the Data Source for information on setting it correctly.
You should then see the Query Options dialog box, see Figure 17. Two pages may be selected from this dialog box. You switch pages by clicking on the Filter Records tab or the Sort Records tab. Click on the Sort Records tab.
The most common sort is the one shown in Figure 17, but you may sort the records anyway you choose. The sort is currently set to 'Order by ZIP from lowest to highest, and then by LAST from a to z'. When you are done with the sort criteria click OK. You are now ready to merge.
The merge is now running, please wait a little, since the merge may take some time. When the merge is complete you should see the new mailing label document. Review this document to make sure it contains what you want. When you are ready, print the document.
Now you can exit the MS Word program by choosing File >> Exit. Word will ask if you want to save changes to Labels, say No. Next, Word asks if you want to save changes to merge.doc, say Yes. Excel now asks if you wish to save changes to temp.xls, say No.
Both Excel and Word have extensive help files that you may access by choosing Help >> Contents and Index. Often the index is the place to start since it helps one learn the terminology of one's question.
It is possible to perform this merge in other ways. The approach followed by this document was chosen for both ease of use and stability. One of the other approaches uses MS Merge to get the data from Excel. Unfortunately, I have found that MS Merge frequently crashes or gives incorrect results. Maybe in its next version MS Merge will be the better solution.