• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Mail Merge with no blank lines for missing data

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.
2 Solutions
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

Jerry PaladinoCommented:
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

qsysllcAuthor Commented:
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.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now