Power Query Problems

How to Combine Duplicate Rows in Excel While Preserving Unique Column Values

Steve K Oct 19, 2026 869 views

Question details

The user needs to merge rows with duplicate identifiers into a single row while keeping the distinct values from other columns intact, without summing them up or losing data from uneven ranges.

How to Combine Duplicate Rows in Excel While Preserving Unique Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating datasets containing repeated sample identifiers across different columns while retaining exact unique values.
Observed behavior: Standard pivot tables or consolidation methods often incorrectly sum the values or create duplicates when matching unrelated data columns.

Before you start

Ensure your dataset is organized with clear column headers and consider sorting your identifier column to easily verify the merged results.

Solution 1Recommended

Use Power Query to Merge Data Groups

Power Query allows you to separate data into groups and perform a Full Outer Join, keeping all unique column values intact.

This method is ideal for uneven datasets where certain samples do not have data points for every column combination. By using a Full Outer Join, you ensure no identifiers are lost.

Load your data into Power Query

Select your data range, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range'.

Group your identifiers

Load each identifier-and-value group as a separate query within the Power Query Editor.

Merge the queries

Click 'Merge Queries as New', then select your identifier column (e.g., 'Ind') as the matching key for both tables.

Apply a Full Outer Join

In the Join Kind drop-down menu, select 'Full Outer (all rows from both)'. This ensures identifiers that only appear in one group are preserved.

Expand and load

Click the expand icon on the merged column to reveal unique columns, uncheck 'Use original column name as prefix', and click 'Close & Load' to output the combined data.

Solution 2

Use Power Pivot to Consolidate Rows

If you prefer using PivotTables, Power Pivot can combine duplicate rows into a single view by adjusting field settings to prevent unwanted calculation.

Free Microsoft Office alternative

Manage Your Data Seamlessly with WPS Office

If complex add-ins like Power Pivot or Power Query slow down your workflow, consider WPS Office. It provides powerful data management and PivotTable capabilities in a lightweight, free office suite.

1. Download and Install: Get WPS Office Free from the official website and install it on your device.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx dataset directly.
3. Analyze Data: Use the intuitive Data and Insert tabs to instantly create PivotTables and manage your rows without complex add-in configurations.

Fully compatible with Microsoft Excel (.xlsx) formulas and formatting.Built-in, easy-to-use PivotTable tools for fast data consolidation.Lightweight software that runs smoothly on all Windows, Mac, and mobile devices.

Frequently Asked Questions

Why does my PivotTable show 'Sum of' instead of my actual values?

When adding numerical columns to the Values area in a PivotTable, Excel automatically applies a 'Sum' aggregation. You can right-click the field settings to change the calculation type or manually edit the column headers.

Are Power Query and Power Pivot free in Excel?

Power Query is built into most modern versions of Excel (Excel 2016 and later) under the Data tab. Power Pivot is also included in many versions but may need to be manually enabled via the COM Add-ins menu.

How does a Full Outer Join work for merging duplicate rows?

A Full Outer Join compares two datasets and retains all rows from both. If an identifier exists in one dataset but is missing in the other, the resulting merged table will still display the identifier alongside blank or null values for the missing data points.

Excel & Data Problems