How to transpose duplicate contact portfolios into separate columns in WPS Spreadsheets
Question details
The user needs to restructure duplicated Contact ID and portfolio data so that each unique Contact ID appears once, with all assigned portfolios spilled into separate columns using a dynamic-array formula.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a list where the same Contact ID appears on multiple rows with different portfolio values and wants a wide layout with one row per Contact ID.
- Observed behavior
- The source data currently has repeated Contact IDs across rows, and the user wants a formula-driven spill range that creates one row per Contact ID with portfolios placed in separate columns.
Identify the columns that contain Contact IDs and portfolio values, and confirm that your WPS Spreadsheets version supports dynamic array functions such as REDUCE, LAMBDA, and TOROW. Leave enough empty cells to the right and below the formula cell for the spill range.
Use REDUCE, FILTER, and TOROW to spill portfolios into separate columns
Use this single dynamic-array formula when you want WPS Spreadsheets to automatically create one row per Contact ID and place all matching portfolios across columns.
This formula uses UNIQUE to create one row for each Contact ID, FILTER to retrieve the related portfolio values, TOROW to transpose those values across columns, and REDUCE/VSTACK to combine each Contact ID row into one spill range.
Open your workbook in WPS Spreadsheets. Select the Contact ID column and type CID in the Name Box, then select the portfolio column and type AID. Alternatively, adjust the formula to use actual ranges such as A2:A100 and B2:B100.
Click the cell where the result table should start, paste =DROP(REDUCE("",UNIQUE(CID),LAMBDA(accumulator,id,LET(portfolios,FILTER(AID,CID=id),IFERROR(VSTACK(accumulator,HSTACK(id,TOROW(portfolios))),"")))),1), and press Enter.
WPS Spreadsheets spills one row per unique Contact ID, with each matching portfolio placed in a separate column. The output updates automatically when the source data changes.
Restructure contact portfolios with dynamic arrays in WPS Spreadsheets
Use WPS Spreadsheets to generate one row per Contact ID and split portfolio values into separate columns with a single dynamic-array formula—no manual copying or VBA required.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains the Contact ID and portfolio columns.
- 2. Select the result cell: Click an empty cell with enough blank cells to the right and below for the spill range, for example E2.
- 3. Name the source ranges: Select the Contact ID column and type CID in the Name Box, then select the portfolio column and type AID. If you prefer, edit the formula to use cell ranges instead.
- 4. Enter the dynamic-array formula: Paste =DROP(REDUCE("",UNIQUE(CID),LAMBDA(accumulator,id,LET(portfolios,FILTER(AID,CID=id),IFERROR(VSTACK(accumulator,HSTACK(id,TOROW(portfolios))),"")))),1) into the selected cell and press Enter.
- 5. Review the spilled table: Confirm that one row per unique Contact ID appears, with each portfolio in its own column.
Frequently Asked Questions
Why does the formula return a #SPILL! error in WPS Spreadsheets?
A #SPILL! error means something is blocking the spill range. Clear all cells to the right and below the formula cell, or move the formula to a location with enough empty space.
Can I use Excel table column references instead of named ranges?
Yes. Replace CID with your table's Contact ID column reference, such as Table1[Contact ID], and replace AID with Table1[Portfolio].
What if my WPS Spreadsheets version does not support REDUCE or LAMBDA?
Update WPS Office to the latest version. If your version still does not support dynamic array functions, use Power Query or helper columns to group and transpose the values.
How can I sort the result by Contact ID?
Wrap the unique IDs in SORT: use SORT(UNIQUE(CID)) inside the REDUCE formula instead of UNIQUE(CID).
Do I need to copy the formula down for each Contact ID?
No. The dynamic array formula spills automatically to create one row for each unique Contact ID; do not copy it down.
