How to Combine Names with the Same Address in Excel
Question details
The user needs a method to consolidate an Excel list by grouping duplicate addresses and combining the names of the people living at each address into a single result.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a contact list or mailing database where multiple individuals share a single residence, requiring a condensed view of one address per row with all associated names merged.
- Observed behavior
- The current dataset lists each person on a new row with repeating addresses, whereas the goal state is a grouped dataset with unique addresses and aggregated name fields.
Ensure your dataset has clear column headers for names and addresses, and remove any entirely blank rows to prevent grouping errors during data consolidation.
Use Power Query to Group Records by Address
Power Query is the most efficient method for grouping data and combining text in Excel, allowing you to bypass complex formulas entirely.
By utilizing Excel's built-in Power Query editor, you can group records by a specific column (like Address) and then customize the aggregation method to merge text values (like Names).
Select your entire data range containing the names and addresses, and press Ctrl + T to convert it into a designated Excel Table.
Navigate to the Data tab on the Excel ribbon and click 'From Table/Range' to open your dataset in the Power Query Editor.
Select the Address column, click 'Group By' on the Home tab. In the dialog, set the New column name to 'Combined Names' and choose 'All Rows' as the operation. Click OK.
Go to the formula bar in the Power Query Editor and modify the generated M code. Replace the 'All Rows' aggregation part with 'Text.Combine([NameColumn], ", ")' to join the names together.
Click 'Close & Load' on the Home tab. Excel will create a new worksheet displaying the unique addresses alongside the combined names.

Use Formulas to Extract and Combine Names
If you are using an older version of Excel or prefer a function-based approach, you can extract unique addresses and use an array formula to merge the associated names.
Manage Complex Spreadsheets Efficiently with WPS Office
If you are looking for a reliable and cost-effective way to organize large datasets, WPS Office Spreadsheet provides advanced formula support and duplicate removal tools to handle data consolidation tasks seamlessly.

Frequently Asked Questions
Can I combine names for the same address using an Excel PivotTable?
Standard PivotTables are designed to aggregate numerical data rather than text. However, if you add your data to the Excel Data Model when creating the PivotTable, you can use the DAX function CONCATENATEX to group and combine text fields like names.
Why does my Power Query 'Group By' function only count the names instead of combining them?
By default, the Power Query 'Group By' menu only offers mathematical aggregations like Sum or Count. To combine text, you must select 'All Rows' in the dialog box, and then manually edit the M code in the formula bar to use the 'Text.Combine' function.
Is there a single Excel formula to group addresses and join names?
If you have Excel 2019 or Microsoft 365, you can achieve this with a combination of the UNIQUE and TEXTJOIN functions. Use UNIQUE to get the distinct addresses, and then use TEXTJOIN nested with an IF or FILTER function to pull and combine the corresponding names.




