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

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.

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.

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?

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).