+ Reply to Thread
Results 1 to 4 of 4

Mail Merge If Else ....

  1. #1
    Registered User
    Join Date
    06-20-2012
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    29

    Mail Merge If Else ....

    Hi

    I have a mail merge document which I need to pull in two separate fields from excel, e.g. Insurance and Membership.

    The data is many rows (employees), some of which have Insurance, some of which have membership and some of which have both.

    The memo should read something like :

    Dear xxx

    Your amounts for this month are as follows:

    Membership: [FIELD VALUE IF APPROPRIATE]
    Insurance: [FIELD VALUE IF APPROPRIATE]

    Thank you


    However, I want to be able to merge the document so that only those that are relevant to the employee show on their memo. For example, Bob may have insurance but not membership, so his memo would read as above, but without the option Insurance. Jane may have both so both headings would appear with the value next to it. I want to avoid producing memos that have a heading with a blank field next to it.

    Can anyone point me in the right direction please.

    Thank you

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Mail Merge If Else ....

    One of the options in mail merge is not to show data that is missing.

    If you can, re-arrange your data so you have
    Cost Type 1 | Amount 1 |Cost Type 2 | Amount 2

    Now on the Mail Merge template use
    [Cost Type 1] [Amount 1]
    [Cost Type 2] [Amount 2]

    If the cost type and amount are blank, mailmerge will ignore the line.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Mail Merge If Else ....

    You need to modify your mailmerge main document as follows:

    Dear {MERGEFIELD Person}

    Your amounts for this month are as follows:

    {MERGEFIELD Membership \b "Membership: " \f "¶
    "}{MERGEFIELD Insurance \b "Insurance: " \f "¶
    "}

    Thank you
    where ¶ is a real paragraph break. Note how the 'Membership: ' & 'Insurance: ' are now both within the MERGEFIELD codes. You can access them for editing via Shift-F9.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Mail Merge If Else ....

    Quote Originally Posted by dflak View Post
    One of the options in mail merge is not to show data that is missing.
    That setting - which is the default - will only suppress the line if there is nothing else on that line, which isn't the case here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mail Merge Directory - Creating merge with multiple lines
    By tiggynook in forum Excel General
    Replies: 3
    Last Post: 05-03-2017, 03:00 PM
  2. Replies: 0
    Last Post: 06-24-2015, 02:34 AM
  3. Replies: 0
    Last Post: 01-05-2015, 07:35 AM
  4. Replies: 2
    Last Post: 01-22-2013, 11:37 AM
  5. Replies: 2
    Last Post: 07-12-2012, 08:11 PM
  6. Mail Merge Query - Format of Merge Fields in Word
    By carlosbourn in forum Excel General
    Replies: 2
    Last Post: 11-10-2007, 07:11 AM
  7. Replies: 0
    Last Post: 04-21-2006, 03:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1