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.
Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
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 PaladinoConnect With a Mentor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.