Power Query Problems

How to Combine Matching Rows and Unique Values in Excel

Elise Williams Oct 9, 2026 872 views

Question details

The user needs to group data with repeated project titles and combine their unique corresponding values from another column into a single cell without simply merging cells.

Combine Matching Rows and Unique Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing project data where multiple rows share the same project details but have unique values in a specific column that need to be grouped together.
Observed behavior: Shared project information should appear once, while unique values are combined in a single cell, keeping the dataset filterable and analyzable.

Before you start

Before combining rows, ensure your data is sorted by the project title or the main grouping column, and convert your data range into a formal Excel Table (Ctrl+T) for dynamic updates.

Solution 1Recommended

Use Power Query to Group and Combine Values

Power Query is the safest and most efficient method to group matching rows and combine unique values into a single cell without breaking data structure.

Using Power Query avoids the pitfalls of standard cell merging, which breaks filtering and sorting. It dynamically processes all projects at once, ensuring your dataset remains clean and analysis-ready.

Load data into Power Query

Select any cell in your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Group rows by project

Select the column that defines your project (e.g., Project Title). Go to the Transform tab and click 'Group By'. In the dialog, set the New Column Name to 'GroupedData' and choose 'All Rows' as the Operation.

Extract and combine unique values

Go to the Add Column tab and click 'Custom Column'. Use the formula =Table.Column([GroupedData], "Column J") (replace 'Column J' with your actual column name). Click OK.

Extract values with a delimiter

Click the double-arrow extract icon on the header of your new custom column. Choose 'Extract Values' and select your preferred delimiter, such as a comma or semicolon.

Load the transformed data

Remove any unnecessary columns, then click 'Close & Load' on the Home tab to output the combined data to a new worksheet.

Use Power Query to Group and Combine Values

Dynamic Updates: When you add new data to the original table, simply right-click the Power Query output table and select 'Refresh' to instantly update the combined values.

Solution 2

Use Dynamic-Array Formulas (TEXTJOIN and FILTER)

If you prefer not to use Power Query, modern Excel functions can extract unique projects and combine their related values dynamically.

Solution 3

Use a VBA Macro (For Final Layouts Only)

A macro can physically merge cells sharing the same project title, though this is strictly for visual formatting as it makes data difficult to filter.

Data Management Made Easy

Easily Group Complex Data with WPS Spreadsheet

WPS Office provides powerful built-in tools like dynamic arrays, advanced pivot tables, and comprehensive data management features to seamlessly group matching rows and consolidate unique values.

1. Open your dataset: Launch WPS Spreadsheet and open your existing data file.
2. Extract distinct items: Use the UNIQUE function in a new summary area to pull distinct project names automatically.
3. Combine the values: Apply the TEXTJOIN and FILTER formula combination to aggregate your unique values without merging cells.

Fully compatible with Microsoft Excel formulas and formats.Supports modern dynamic-array functions like TEXTJOIN, UNIQUE, and FILTER.Lightweight software with a user-friendly interface for effortless data analysis.Free alternative that handles large datasets without freezing or lagging.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I just use the 'Merge Cells' button for matching rows?

Merging cells physically combines them, which breaks essential Excel features like sorting, filtering, and PivotTables. Using Power Query or dynamic formulas retains the underlying data structure while visually grouping the information perfectly.

Can I combine unique values with a specific separator like a line break?

Yes. In Power Query, you can choose a 'Custom' delimiter during the extract phase and type a specific character. If using a formula, use CHAR(10) as the delimiter in your TEXTJOIN function (e.g., =TEXTJOIN(CHAR(10), TRUE, FILTER(...))) and ensure 'Wrap Text' is enabled on the cell.

Will the combined values update automatically when my source data changes?

It depends on the method used. If you use dynamic-array formulas (TEXTJOIN and FILTER), the results update instantly. If you use Power Query, you will need to right-click the output table and select 'Refresh' (or click Refresh All on the Data tab) to see the latest changes.

Excel & Data Problems