Function Problems

How to Combine Values from Duplicate Rows into One Row in Excel

Camila Milosovich Oct 10, 2026 870 views

Question details

The user needs to consolidate values distributed across multiple duplicate rows into a single row per unique item, keeping the original column structure intact.

How to Combine Values from Duplicate Rows into One Row in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing and cleaning up a dataset where information for the same item is scattered across multiple rows, requiring a consolidated, single-row summary for each item.
Observed behavior: Values for the same item are currently split across multiple rows and columns, creating a fragmented dataset with duplicate row names and scattered data points.

Before you start

Ensure that your dataset does not contain merged cells and that your column headers are clearly defined before applying dynamic array formulas like UNIQUE and FILTER.

Solution 1Recommended

Use UNIQUE, FILTER, and SUMIF Formulas

This method extracts a list of unique items and aggregates their corresponding numeric values into a single row without losing your data structure.

By combining the UNIQUE and FILTER functions, you can generate a clean list of item names devoid of blanks and duplicates. Then, the SUMIF function is used to pull all scattered values associated with that unique item into the corresponding adjacent columns.

Extract a list of unique items

Select a blank cell (e.g., AF2) where you want the consolidated list to begin. Enter the formula `=UNIQUE(FILTER('Sheet 1'!A2:A1000,'Sheet 1'!A2:A1000<>""))`. This will filter out any blank cells and generate a vertical list of unique row labels.

Enter the SUMIF formula to aggregate data

In the adjacent column next to your first unique item (e.g., cell AG2), enter the formula `=SUMIF($A$2:$A$1999,$AF2,B$2:B$1999)`. The reference `$A$2:$A$1999` is your original item column, `$AF2` is your new unique item, and `B$2:B$1999` is the column containing the values to pull.

Fill the formula across and down

Select cell AG2, click and drag the fill handle (the small square at the bottom-right corner of the cell) across the columns to match your original headers, and then drag it down to the bottom of your unique items list to populate all consolidated values.

Mind the Absolute References: Pay close attention to the dollar signs ($) in the SUMIF formula. They lock the column and row references so that when you drag the formula across and down, it targets the correct data ranges without shifting.

Data Consolidation in WPS Spreadsheet

Combine and Consolidate Data Quickly in WPS Office

WPS Spreadsheet fully supports advanced array functions like UNIQUE, FILTER, and SUMIF, allowing you to seamlessly combine duplicate rows. It also offers built-in Data Consolidation tools for a formula-free approach.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the scattered data.
2. Apply dynamic array formulas: Type `=UNIQUE(A2:A1000)` in a new column to instantly generate a duplicate-free list of your items.
3. Use SUMIF to merge values: In the adjacent cell, use `=SUMIF()` to pull and sum the scattered data for each unique item.
4. Save and export: Save your consolidated file seamlessly in Excel format (.xlsx) to maintain compatibility with other users.

Fully supports advanced dynamic array formulas for quick data extraction.Highly compatible with Microsoft Excel (.xlsx) file formats and syntax.Features a built-in 'Consolidate' tool for users who prefer a UI-driven merging process.Free, lightweight, and user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine duplicate rows without using formulas?

Yes. Navigate to the 'Data' tab and click on the 'Consolidate' button. This built-in tool allows you to summarize data from separate ranges and consolidate the results into a single output range based on row or column labels without typing any formulas.

Why is the UNIQUE function returning a #NAME? error?

The UNIQUE function is only available in newer versions of Excel (like Microsoft 365 or Excel 2021) and updated versions of WPS Office. If you are using an older version, the software will not recognize the function and will return a #NAME? error.

How do I combine text values from duplicate rows instead of summing numbers?

The SUMIF function only works for numeric values. To combine text values from duplicate rows into one cell, you can use the TEXTJOIN function combined with an IF statement entered as an array formula, such as `=TEXTJOIN(", ", TRUE, IF($A$2:$A$100=$AF2, $B$2:$B$100, ""))`.

Does filtering blanks with the UNIQUE function alter my original data?

No, using `=UNIQUE(FILTER(A2:A1000,A2:A1000<>""))` simply generates a new array of unique values in the destination cells. It does not delete, alter, or hide any data in your original dataset.

Excel & Data Problems