You are here:

Microsoft Word/IF statement in WORD merging from Excel spreadsheet

Advertisement


Question
Help! Have WORD 2007. Must make a community directory but so much is duplicated information in spreadsheet that I don't know how to tell WORD not to print same exact info over and over.  WORD Form look like.
Hus&WifeName   Address  HusPh   HusCell   HusEmail
         HusName   HusPh  HusCell   HusEmail
         WifeName  WifePh  WifeCell  WifeEmail

If in spreadsheet HusPh & HusCell & HusEmail is SAME as WifePh, etc. then only top line in Word is to print!  If HusPh etc different from WifePh etc then 1st names with Ph Cell etc to be printed on 2nd line.

The IF statement in Merge Rules doesn't work so then I went to equation and inserted brackets but they are all over the place and I don't have F9 key to hit for brackets.  thank you in advance.  Note:  Spreadsheet is done by a large company and can't be touched.  Carol

Answer
Okay, assuming you don't want the husband's phone or cell or email printed on the top line at all if the wife's is different (that is, only if both use it), and assuming that there is actually data in every field (no blank fields), then your form should look like this:

Line 1: { MERGEFIELD Hus&WifeName } { MERGEFIELD Address } { IF { MERGEFIELD HusPh } = { MERGEFIELD WifePh } "Phone: { MERGEFIELD HusPh}" } { IF { MERGEFIELD HusCell } = { MERGEFIELD WifeCell } "Cell: { MERGEFIELD HusCell}" } { IF { MERGEFIELD HusEmail } = { MERGEFIELD WifeEmail } "Email: { MERGEFIELD HusEmail}" }

Line 2: { MERGEFIELD HusName} { IF { MERGEFIELD HusPh } = { MERGEFIELD WifePh } "" "Phone: { MERGEFIELD HusPh}"  } { IF { MERGEFIELD HusCell } = { MERGEFIELD WifeCell } "" "Cell: { MERGEFIELD HusCell}" } { IF { MERGEFIELD HusEmail } = { MERGEFIELD WifeEmail } "" "Email: { MERGEFIELD HusEmail}" }

Line 3: {MERGEFIELD WifeName } { IF { MERGEFIELD HusPh } = { MERGEFIELD WifePh } "" "Phone: { MERGEFIELD WifePh}" } { IF { MERGEFIELD HusCell } = { MERGEFIELD WifeCell } "" "Cell: { MERGEFIELD WifeCell}" } { IF { MERGEFIELD HusEmail } = { MERGEFIELD WifeEmail } "" "Email: { MERGEFIELD WifeEmail}" }

This doesn't quite cover all the bases: it will print the phone numbers and email addresses that are the same on the top line and the ones that are different on the Husband and Wife lines. What it won't do is suppress the husband and wife lines if all three items are the same. I think that can be accomplished by using a nested IF field that tests all three conditions before printing the husband or wife's name. You'd also have to experiment with where to put the paragraph breaks. I think they'll have to be inside the TrueText (or FalseText, whichever results in something being printed) of each line, before the mergefield, rather than at the ends of the lines.

Note that all the IF fields will have to be inserted by hand, using Ctrl+F9, but you can use the Insert Merge Field button on the Review tab of the Ribbon to insert the merge fields.

As I stated in my profile, I'm not really adept at complex merges. Maybe this will get you a little farther forward, but I would suggest asking at http://answers.microsoft.com/en-us/office/forum/word to attract the attention of more knowledgeable users.

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

Microsoft Word

All Answers


Answers by Expert:


Ask Experts

Volunteer


Suzanne S. Barnhill

Expertise

I've been using Word for Windows since version 2.0 (1992), and the more I learn about it, the more I realize how little I know. But I may know a few things that you don't, and I'll help if I can. I answer many questions every day in Microsoft's peer support forums and as a result have been awarded the MVP (Most Valuable Professional) designation by Microsoft Corporation. You may be able to find the answer to your question at the Word MVPs' FAQ site or at my own Word FAQ site, so please check those first! Please, no questions about VBA (macros), Registry editing, networks, or complex merges, as I have no experience with these aspects of Word, nor do I have any experience with Word for Mac. Please indicate which version of Word you are using; if you are not sure, see "What version of Word do I have?".

Experience

I have a master's degree in classics (Latin), which is surprisingly helpful, though I no longer teach. The things I am proudest of: Having raised two children to maturity, both Merit Scholars, both college graduates (one a philosophy major!), one Phi Beta Kappa (from Harvard!); having been made a Paul Harris Fellow by my Rotary club; having been designated a Microsoft MVP.

Organizations
Rotary Club of Fairhope, Friends of the Fairhope Public Library

Education/Credentials
B.A. (Latin), Agnes Scott College, 1966; M.A. (Classics), Emory University, 1972.

Awards and Honors
Microsoft Most Valuable Professional award every year since 1999; many Rotary honors

©2016 About.com. All rights reserved.