How to Combine Rows with Matching Values in WPS Spreadsheets and Excel Power Query
Question details
The user needs to merge multiple rows that share the same value into one row per value, using either native spreadsheet formulas or Power Query.
- Product
- WPS Spreadsheets, Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A table contains repeated values in a key column, and the user wants to consolidate all rows with the same key while joining text or summing numbers in the other columns.
- Observed behavior
- Matching rows currently remain separate; the expected output is one consolidated row per key value with the selected columns merged or aggregated.
Open the worksheet in WPS Spreadsheets or Excel and identify the column with repeated values. Decide which other columns should be summed, counted, or joined as text before choosing a method.
Combine Matching Rows in WPS Spreadsheets with Formulas
Use this when you want a formula-based result that updates automatically and you do not need Excel Power Query.
WPS Spreadsheets supports Excel-compatible functions such as UNIQUE, TEXTJOIN, and SUMIF/SUMIFS. This makes it possible to create a distinct key list and then merge text or total numbers for each key.
Copy the key column to a new sheet, select it, go to Data > Remove Duplicates, and confirm the range to keep only one row per value.
In the adjacent cell type =TEXTJOIN(", ",TRUE,IF($A$2:$A$100=A2,$B$2:$B$100,"")) and press Enter; if needed, confirm as an array formula.
In the next column type =SUMIF($A$2:$A$100,A2,$C$2:$C$100) and press Enter.
Select the formula cells and drag the fill handle down the unique list to apply the aggregation to every key.
Group Rows by Matching Values in Excel Power Query
Use this if you are working in Microsoft Excel and need a repeatable Power Query transformation for large or frequently refreshed tables.
Combine Matching Rows in WPS Spreadsheets Without Power Query
Use WPS Spreadsheets to create a unique key column with Remove Duplicates, then join text with TEXTJOIN and total numbers with SUMIF. It is a free, Excel-compatible way to consolidate rows without needing Excel Power Query.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx or .xls file, and review the table with matching row values.
- 2. Create a unique key list: Copy the key column to a new sheet, select the pasted range, and click Data > Remove Duplicates.
- 3. Join text for each key: In the cell next to the first unique key, enter =TEXTJOIN(", ",TRUE,IF($A$2:$A$100=A2,$B$2:$B$100,"")) and press Enter.
- 4. Sum numbers for each key: In the following column, enter =SUMIF($A$2:$A$100,A2,$C$2:$C$100) and press Enter.
- 5. Fill down and save: Drag the fill handle down the unique list to apply the formulas, then save the workbook in Excel-compatible .xlsx format.
Frequently Asked Questions
Can I combine rows with matching values in WPS Spreadsheets without Power Query?
Yes. Copy the key column to a new sheet, use Data > Remove Duplicates to build a unique list, then use TEXTJOIN to combine text and SUMIF or SUMIFS to total numbers.
How do I group rows in Excel Power Query?
Select the table and go to Data > From Table/Range. In Power Query Editor, select the key column, click Transform > Group By, add aggregation columns, and then Close & Load.
Which formula joins text from multiple rows that share the same key?
Use =TEXTJOIN(", ",TRUE,IF(range=key,text_range,"")) and fill it down the unique key list. In newer spreadsheet versions it spills or returns the joined text for that key.
Does WPS Spreadsheets support Excel .xlsx files for row consolidation?
Yes. WPS Spreadsheets opens and saves Excel .xlsx, .xls, and .csv files, so you can consolidate rows and keep Excel compatibility.
