Power Query Problems

How to Combine Names with the Same Address in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 3, 2026 871 views

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.

How to Combine Names with the Same Address in Excel
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.
Before you start

Ensure your dataset has clear column headers for names and addresses, and remove any entirely blank rows to prevent grouping errors during data consolidation.

Solution 1Recommended

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

1
Convert data to a table

Select your entire data range containing the names and addresses, and press Ctrl + T to convert it into a designated Excel Table.

2
Load into Power Query

Navigate to the Data tab on the Excel ribbon and click 'From Table/Range' to open your dataset in the Power Query Editor.

3
Group by the Address column

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.

4
Combine the text values

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.

5
Load the combined data

Click 'Close & Load' on the Home tab. Excel will create a new worksheet displaying the unique addresses alongside the combined names.

Use Power Query to Group Records by Address
Power Query Availability: Power Query is built directly into Excel 2016 and newer under the Data tab. For Excel 2010 and 2013, it must be downloaded as a free add-in from Microsoft.
Free Microsoft Office alternative

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.

Completely free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Supports advanced data processing functions like TEXTJOIN, INDEX, and SMALLFamiliar user interface ensuring a zero-learning-curve transition
microsoft office alternative - wps office

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.