How to Combine Multiple Values into One Row with TEXTJOIN and FILTER in WPS Spreadsheets
Question details
The user needs to display all values associated with the same person in one cell on the first occurrence, leaving repeated name rows blank in a WPS Spreadsheets worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet contains multiple rows per person with different values, such as cupcake flavors, and the user wants a compact grouped list by person.
- Observed behavior
- Currently each flavor appears in its own row beside a repeated name; the goal state is a single summarized cell per person and blank duplicate rows.
Make sure your WPS Office version supports the FILTER function and that repeated names are sorted or grouped in adjacent rows, because the helper formula compares each row with the previous row.
Use TEXTJOIN and FILTER with IF to Show All Flavors on the First Name Row
Use this method when names are sorted or grouped together and you want a simple formula you can fill down.
This formula filters column B for every row where column A matches the current person, joins those values with a comma and space, and returns an empty string for repeated consecutive names.
Open the workbook in WPS Spreadsheets and click cell C2, or the first cell where the combined result should appear.
Type =IF(A2=A1,"",TEXTJOIN(", ",TRUE,FILTER($B$2:$B$1000,$A$2:$A$1000=$A2))) and press Enter.
Drag the fill handle from C2 down through all rows that contain data, or double-click the fill handle to auto-fill adjacent rows.
Confirm that the first occurrence of each person shows all matching flavors in one cell and subsequent duplicate rows remain blank.
Combine Repeated Row Values in WPS Spreadsheets
Use WPS Spreadsheets to group repeated person rows into one cell with TEXTJOIN and FILTER. The formula-filled column shows the complete list on the first occurrence and blanks duplicate rows, helping you clean up reports quickly.
- 1. Open your workbook: Launch WPS Office and open the spreadsheet that contains the repeated names and values.
- 2. Add the formula: Click the first result cell, enter =IF(A2=A1,"",TEXTJOIN(", ",TRUE,FILTER($B$2:$B$1000,$A$2:$A$1000=$A2))), and press Enter.
- 3. Fill down the column: Drag or double-click the fill handle to copy the formula through the remaining data rows.
- 4. Review the grouped result: Check that the first row for each person contains all values and that duplicate name rows are blank.
Frequently Asked Questions
Why does the formula show an error in WPS Spreadsheets?
Make sure the A and B ranges are the same size and that your WPS version supports FILTER. Also check that the names in column A are sorted or grouped consecutively when using the previous-row comparison method.
Can I use a different separator between the combined values?
Yes. Change the delimiter argument in TEXTJOIN, for example use a semicolon or pipe instead of a comma.
What if my names are not sorted together?
Sort the data by the name column first, or use a dynamic array formula based on COUNTIF and FILTER so it does not depend on previous-row comparison.
How do I apply the formula to more than 1000 rows?
Replace $B$2:$B$1000 and $A$2:$A$1000 with the actual rows you need, such as $B$2:$B$5000 and $A$2:$A$5000.
