How to Combine Spouses at the Same Address in Excel for Mail Merge
Question details
The user wants to combine matching address records for spouses or household members into a single row in an Excel mailing list to streamline the mail merge process.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing a mailing list for mail merge where multiple people share the same household address, requiring consolidation to prevent sending duplicate mail.
- Observed behavior
- Currently, each person has a separate record, leading to duplicate mailings to the same household. The goal is a unified household record containing both names.
Ensure your address data is consistently formatted (e.g., standardizing 'Street' vs 'St.') without typos, as inconsistencies will prevent the software from recognizing matching households.
Use PivotTable to Identify and Group Duplicate Addresses
Group records by address using a PivotTable to easily spot which addresses have multiple names attached to them.
A PivotTable quickly summarizes your mailing list and counts how many times a specific address appears. This allows you to identify households before merging the names manually.
Select your entire mailing list, navigate to the Insert tab, and choose PivotTable. Place it on a new worksheet.
Drag the address fields (Street, City, State, ZIP) into the Rows area to group the records.
Drag the 'Name' field into the Values area. Any address with a count greater than 1 indicates a household with multiple people.
Return to your main mailing list, filter for the identified duplicate addresses, manually edit the primary record to include both names (e.g., 'Mr. and Mrs. Smith'), and delete the extra row.

Use an Excel Formula Helper Column
Create a helper column to concatenate address fields and use the COUNTIF function to find duplicates.
Combine Mailing Addresses Easily with WPS Spreadsheet
WPS Office provides powerful data management tools, including PivotTables, conditional formatting, and advanced formulas, to help you quickly clean up mailing lists and combine household addresses.
- 1. Open Mailing List: Launch WPS Spreadsheet and open your mailing list document.
- 2. Create Helper Column: Use the TEXTJOIN function to combine address fields into a single unique string for each row.
- 3. Highlight Duplicates: Go to the Home tab, click Conditional Formatting, and select Highlight Cells Rules > Duplicate Values to visually spot identical addresses.
- 4. Merge Spouses: Edit the names of highlighted duplicates to include both spouses, then remove the extra rows using the Remove Duplicates tool under the Data tab.

Frequently Asked Questions
Can I automate combining names with Power Query?
Yes, Power Query can group data by address fields. You can use the 'Group By' feature on the address columns and select 'All Rows', then add a custom column using Text.Combine to merge the names before expanding the table.
What happens if the addresses have slight typos like 'St.' vs 'Street'?
Spreadsheet software treats them as different addresses. Before looking for spouses, you should clean your data using Find and Replace (Ctrl+H) to standardize street suffixes and abbreviations.
How do I export the combined list for a Word Mail Merge?
Once you have your consolidated list with one row per household, simply save the file. Open your word processor, go to Mailings > Select Recipients > Use an Existing List, and choose your updated spreadsheet file.
Is there a built-in 'Merge Households' button?
No, there is not a single button to merge households automatically. You must use formulas, PivotTables, or Power Query to identify duplicates and merge the names.




