Preparing Excel for Mail Merge, Mr & Mrs separate rows, same address

[Dunnju66]

Hi, I am working on a mail merge with data source Excel. This Excel includes all recipients in a row, so a husband on one row, and a wife on another but at the same address. I want to send one print mailer to each household - so, I need to merge the first contact name into one field and go to the same address. Are there any major differences between doing this with MAC Excel vs. Windows Excel?

This is a sample set - names and addresses modified for this sample.

Excel Sample Mail merge data.xlsx

My end goal is to have a simple set of instructions that can be applied to Excel each month so we are using one master set of data monthly.
Preferably not in PowerQuery.

Any pointers?

Thank you!

[HansV]

Welcome to Eileen's Lounge!

In the attached version, I added a helper column on the first sheet to create a unique identifier.
I added a second sheet with formulas to return the unique addresses plus the other data.
You can use this sheet for your mail merge.

PS 1: This will only work if you have Microsoft 365 or Office 2021.
PS 2: I have assumed that couples use the same last name. If there are exceptions to that, let me know.

Excel Sample Mail merge data.xlsx

[Dunnju66]

Awesome. Thank you for the quick reply!! Yes, I have 0365.

[p45cal]

In the attached:
1. On sheet p45cal, an adaptation of Hans' solution, differences being (a) joining of names a bit different and (b) one formula per row which spills to the right. The data in the other headers (Membership Type, Membership Due Date etc.) are only the first row's data with that address.
2. A Power Query offering on sheet PQ cell M1 which handles different last names at the same address (row 7 source data changed to demonstrate this where there are 2 people with the same last name and one with a different last name). This was quicker and easier to put together than the formula solution. I can understand people shying away from Power Query because it's a steep learning curve and still relatively new.

[Dunnju66]

Thank you p45cal, this is helpful. I am still scared to try PQ though. :-/ I'm just an Excel user and when I see formulas and PQ my heart starts racing! :-/

[Dunnju66]

Hi Hans and/or p45cal -

I have applied the formula for the mail merge and it works! there are two things I'd like to figure out -
1) how to remove duplicates, for example in my master list there are many Mr & Mrs that have been combined and now they are showing up twice - is there a way to filter them out?

130 E. 6th|98034 Berndt, Jill and Bob
130 E. 6th|98034 Berndt, Jill and Bob
123 West Street Bill Black
123 West Street Lori White

This is the formula I'm using.
=XLOOKUP($A13, Sheet1!$H$2:$H$408, Sheet1!$A$2:$A$408)&", "&TEXTJOIN(" and ", TRUE, FILTER(Sheet1!$B$2:$B$408, Sheet1!$H$2:$H$408=$A13))


2) what I add to the formula (not PQ) to address different last names at the same address?

Appreciate your guidance.

[HansV]

See the new version.

Excel Sample Mail merge data.xlsx

[Dunnju66]

Thanks for the quick response, Hans. I will review and apply to my master and see if I can make it work.
You amaze me with how you so quickly can write a formula considering many requirements. Thank you!

[Dunnju66]

Hi Hans,

You helped with a formula for combining Mr & Mrs with the same address which I appreciate and I'm getting closer to getting this to work for me although, I am stuck on why duplicates are still showing up?

I tried with a new Excel to get the formula to work for me and I am stuck on why the duplicates are still showing up. They weren't on yours - can you take a quick look and tell me what I'm missing in the formula? Thank you! Judy

Test Excel formula.xlsx

[HansV]

Since the columns are in a different order here, the formulas have to change accordingly.
Also, you didn't use the UNIQUE formula to return unique identifiers...

Test Excel formula.xlsx

[Dunnju66]

Thanks Hans!!

[Dunnju66]

Hi Hans,

I have got the formula to work on my master sheet with 371 members. My question is if there are a few modifications that I could make to the formula that would change the order of the members to be first name, last; and if two people are at the same address ...first, last would be on separate lines. I am preparing a mail merge for address labels and ideally want them as

Jim & Judy Smith
address

or

Jim Smith
Judy White
address

Formula:

=LET(
f,FILTER(Members!$A$2:$B$371,Members!$G$2:$G$371=$A2),
IF(ROWS(f)=1,
INDEX(f,1,1)&", "&INDEX(f,1,2),
IF(INDEX(f,1,1)=INDEX(f,2,1),
IF(INDEX(f,1,2)=INDEX(f,2,2),
INDEX(f,1,1)&", "&INDEX(f,1,2),
INDEX(f,1,1)&", "&INDEX(f,1,2)&" and "&INDEX(f,2,2)
),
INDEX(f,1,1)&", "&INDEX(f,1,2)&" and "&INDEX(f,2,1)&", "&INDEX(f,2,2)
)
)
)

Thanks again for your guidance.
Judy

[HansV]

Try this:

Code: Select all

=LET(
    f, FILTER(Sheet1!$A$2:$B$34, Sheet1!$G$2:$G$34=$A2),
    IF(ROWS(f)=1,
        INDEX(f, 1, 2)&" "&INDEX(f, 1, 1),
        IF(INDEX(f, 1, 1)=INDEX(f, 2, 1),
            IF(INDEX(f, 1, 2)=INDEX(f, 2, 2),
                INDEX(f, 1, 2)&" "&INDEX(f, 1, 1),
                INDEX(f, 1, 2)&" & "&INDEX(f, 2, 2)&" "&INDEX(f, 1, 1)
               ),
            INDEX(f, 1, 2) &" "&INDEX(f, 1, 1)&CHAR(10)&INDEX(f, 2, 2)&" "&INDEX(f, 2, 1)
        )
    )
)

[HansV]

Here is your sample workbook with the new formula:

Test Excel formula.xlsx

[Dunnju66]

Thank you so much Hans...I have been applying this new formula and almost have it working properly. I'm getting very close.