+ Reply to Thread
Results 1 to 11 of 11

Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Hello

    I have an excel spreadsheet that contains contact details which are listed as groups 1 to 6 with each group having 16 slots that can be populated by a contacts details which may be paired together to give 8 pairs per group.

    I want to send a template letter to those pairs informing them who the other pair is.

    Eg Hi (Lesley)

    I am pleased to inform you that (Village 56) have been selected. You are paired with (Village 36). Their Coordinator’s details are :- (Anne, phone number and email contact details)


    and then reciprocated with the same letter to the second in the pair:

    Hi (Anne)

    I am pleased to inform you that (Village 36) have been selected. You are paired with (Village 56). Their Coordinator’s details are :- (Lesley, phone number and email contact details)


    However, not all cells are paired so they would get a different letter saying somthing to the effect:

    Hi (First Name)

    I am pleased to inform you that (village (x)) have been selected. Usually your village would be paired with another community but at this time you will be on your own.


    It's also worth noting that not all groups contain the full 16 villages, some may contain , for example, only three villages, none of whom are paired with anyone in the same group.

    So, my questions...

    How do I create the pairing relationship between villages?
    How do I get mailmerge to differentiate between paired and single villages?
    How do I mailmerge the pairings so each pair gets the correct paired letter as per my example above?

    Many thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    OK, so to populate the pairings I just used a simple helper column which appears in the mailmerge fields Eg Cell H8 = C9 and Cell H9 = C8 which is then cascaded down for each pairing respectively.

    My issue now is to identify those rows that are not paired with anyone and redirect to the 'single' template letter.

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

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Your workbook isn't properly structured for a mailmerge, so you won't be able to use that approach unless you change its structure to suit. See: https://support.office.com/en-us/art...B-0A948FA3D7D3

    Once you sort that out, you could use a countif test in Excel you count the number of clients assigned to each pair. The mailmerge main document could then use an If test to determine which text to output according to the number returned by the countif, combined with a DATABASE field to list all (or only the other) members of the pairs, where appropriate.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    So I made some changes and with a bit of thought came up with the revised formulas to show whether a village was 'paired', 'single' or whether the slot was empty (just blank).
    I think I can then use this as the qualifier in the mail merge as to which letter is sent.
    Attached Files Attached Files

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

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    There is still no apparent way of relating any pairings via a mailmerge; with your current layout, it would be possible possible via a macro, though. Even then, it's not apparent why each member of a pair is the other's coordinator.

  6. #6
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Hi Macropod
    Thanks for your suggestions (and a very Merry Xmas / Happy New Year).
    Each team coordinator is given the contact details of the coordinator of the team they are paired with (which will be populated from the spreadsheet once I've finished it) as they need to make contact with each other in order to facilitate handing over equipment that they have been lent. (I used the term 'villages' arbitrarily just as a label to help identify which team was which rather than just using team numbers which makes ones head hurt after staring at them for a while.
    I think I have achieved my aim using the Index/ Match function as per the newly uploaded files. What do you think?

    My only problem now is how do I get rid of the N/A where a 'village' is not teamed with anyone and then to suppress the blank rows so they are not included in the mailmerge?
    Attached Files Attached Files

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

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Try your latest workbook with the attached mailmerge main document - which you'll need to reconnect to your data source.

    Note: It is assumed your mailmerge main document and excel workbook are stored in the same folder.
    Attached Files Attached Files
    Last edited by macropod; 12-27-2018 at 03:46 AM.

  8. #8
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Thanks for looking at this. Not sure what you have done but sorry to say the result is not what I was expecting.
    When previewing the merge, the name of the second village to be paired with is now missing.
    Also, the name of the paired coordinator with Lesley should appear as Anne. Instead, Catherine is displayed and remains constant throughout.
    If I run the merge with the option to edit (to preview the layout) then the above still applies but the name Catherine is replaced with the name of the recipient of the letter ie Lesley gets a letter telling her she is paired with Lesley, Anne gets a letter telling her she is paired with Anne etc :-(
    Ruth gets a letter correctly stating that her team are on their own and is not paired with anyone... but then because the next row is not populated, a letter is still printed (minus the mergefields - which I was hoping to suppress).
    Attached Images Attached Images

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

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Attachment updated. Try it now. Do note that, to see the correct results, you need to complete the merge, not just preview it.

  10. #10
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Quote Originally Posted by macropod View Post
    Attachment updated. Try it now. Do note that, to see the correct results, you need to complete the merge, not just preview it.
    This is excellent and now appears to be working as I intended. Thankyou for the work you have done on this. I changed the sentence structure slightly to read as follows:

    "Hi (Lesley)

    I am pleased to inform you that (Village 56) have been selected. You are paired with (Village 36). Their Coordinator’s details are :- (Anne, phone number and email contact details)"


    It's important that the sentence is structured this way rather than the paired village name placed in the Coordinators contact details as you had done.

    Many thanks again for giving up your time to help me with this.
    Last edited by Smudge.Smith; 12-27-2018 at 03:18 AM. Reason: Resolved

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

    Re: Mailmerge Groups (Identifying pairings and singles and sending approriate letter)

    Try the latest attachment update.

+ 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. [SOLVED] Identifying groups of lines between start and end indicators
    By beral in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2018, 10:15 AM
  2. Help with identifying groups and sets
    By needyohelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2016, 12:53 PM
  3. PLEASE HELP - Create the approriate function
    By NoJokenVA in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-14-2013, 03:04 PM
  4. Sending to groups in outlook
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2006, 02:15 PM
  5. Identifying drive letter with Save-as to a server
    By excelnut1954 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-29-2005, 02:10 PM
  6. Identifying the user's drive letter
    By Ben in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2005, 11:40 AM
  7. [SOLVED] Identifying and separating groups of data.
    By Matt in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-24-2005, 12:05 AM

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