Mail Merge with no blank lines for missing data

Posted on 2010-01-05
Last Modified: 2012-05-08
I am attempting to set up a Mail Merge using Excel and Word 2007 on Win XP.
The XL data file looks something like:

Name      Jan 1          Jan 15          Feb 5...
Frank     $100.00                              $5.70
Ethyl                            $10.25         $15.35
Tony       $23.00        $12.75          $50.35

The Word document is to include the dates and amount but only if the amounts are non zero.

For Frank the letter would include:
Jan 1         $100.00
Feb 5             $5.75
and Jan 15 would not be shown.

For Ethyl:
Jan 15         $10.25
Feb 5           $15.75
and Jan 1 would not be shown.

For Tony there would be all 3 items.  The table actually contains many more rows and columns but the types of data are the same as shown above.

How do I set up the merge to show the dates and amounts for any entries where the amount is non-zero and not show a date with no amount where the amount is zero?  I don't care whether it is a table or simply a list of dates and amounts.
Question by:qsysllc
    LVL 76

    Accepted Solution

    You need use an IF field to test for zero. Show the field codes in the document by using the Alt+F9 toggle.
    You will need to get something like that looks like this:

    { IF  {MERGEFIELD Amount} <> 0 {MERGEFIELD PayDate} }


    { IF  {MERGEFIELD Amount} <> 0 {MERGEFIELD Amount} }

    You must enter the {} pairs with Crtl+F9 or by entering the field

    LVL 16

    Assisted Solution

    by:Jerry Paladino
    In WORD 2007 the Rules Dialog box makes entering the IF tests a little easier.  As Graham mentioned - Ctrl+F9 to display Field Codes (toggle) and then place your cursor where you want the IF statement and invoke the Rules dialog by:  
    Mailings menu / Write & Insert Field / Rules
    See attached graphics


    Author Closing Comment

    The initial response from Graham Skan was very good.  It provided the strategy I needed.  The response from ProdOps was also helpful in that it helped me with the syntax.  One minor correction - I couldn't get the "<> 0" condition to work so I switched it to "> 0".

    Thank you both for the help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    In a previous video Micro Tutorial here at Experts Exchange (, I explained how to get a free, one-month trial of Office …

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now