How to Transpose Repeated ID Measurements into Excel Columns in WPS Office
Question details
The user needs to convert multiple measurement rows for each ID into a single row per ID with alternating date and measurement value columns.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a normalized measurement table with repeated IDs, dates, and values, and wants to flatten it into a one-row-per-ID reporting layout.
- Observed behavior
- The desired output shows each unique ID once, followed by its date and measurement values placed in consecutive columns.
Open the WPS Office spreadsheet that contains the repeated ID measurement table and verify that the source data is in three consistent columns: ID, date, and measurement value. Reserve a new worksheet or empty columns to the right so the formula output does not overwrite the source data.
Transpose repeated ID measurements with WPS array formulas
Use this method when you need a reusable, formula-driven result that automatically blanks out missing measurement pairs.
This approach uses INDEX, SMALL, and IF in WPS Office array formulas. It does not require Microsoft 365-only functions such as TOROW or REDUCE, so it works in current WPS Office Spreadsheet versions. Replace Sheet1!$A$2:$A$100, $B$2:$B$100, and $C$2:$C$100 with your actual source range.
In a new worksheet, copy the source ID column, select the copied range, and click the Data tab > Remove Duplicates to keep one row per ID.
Click the first output date cell, for example B2, and enter =IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=$A2,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),INT((COLUMN()-COLUMN($B$2))/2)+1)),"") and press Ctrl+Shift+Enter.
Click the first output value cell, for example C2, and enter =IFERROR(INDEX(Sheet1!$C$2:$C$100,SMALL(IF(Sheet1!$A$2:$A$100=$A2,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),INT((COLUMN()-COLUMN($B$2))/2)+1)),"") and press Ctrl+Shift+Enter.
Select B2:C2, drag the fill handle to the right for as many measurement pairs as any ID has, then fill the formulas down beside each unique ID.
Manually copy and paste transpose for a small ID set
Use this quick method when you have only a few IDs and prefer a no-formula workflow.
Transpose Repeated ID Measurements with WPS Office Spreadsheet
WPS Office Spreadsheet lets you reorganize repeated ID measurement rows into a single row per ID using standard formulas, without relying on Microsoft 365-only dynamic array functions. The workbook remains fully compatible with Microsoft Excel .xlsx format.
- 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office, click Menu > Open, and select the .xlsx file that contains the repeated ID measurement rows.
- 2. Prepare the unique ID output column: Copy the ID column into a new sheet, select the copied IDs, and click Data > Remove Duplicates to get one row per ID.
- 3. Create the first date-value formula pair: In the first date cell, enter the date formula; in the first value cell, enter the value formula. Press Ctrl+Shift+Enter after each formula to confirm it as an array formula.
- 4. Fill the output range: Select the first date and value cells together, then drag the fill handle right and down to generate alternating date and value columns for all IDs.
Frequently Asked Questions
Does this work in WPS Office without Microsoft 365 dynamic array functions?
Yes. The recommended method uses INDEX, SMALL, and IF array formulas with Ctrl+Shift+Enter, so you do not need TOROW, REDUCE, or LAMBDA.
What should I do if some IDs have more measurement rows than others?
Add enough output columns to cover the largest number of pairs. The IFERROR function returns blank cells for missing pairs, so shorter ID records do not show formula errors.
How can I keep the output dates formatted correctly?
Select the generated date cells, click Home > Number Format, and choose Short Date or Long Date. WPS Office treats date values with the selected format.
Can I use the formula method with a WPS data table instead of fixed ranges?
Yes. Select the source range, press Ctrl+T to create a table, and use the table column references in the formulas so ranges expand automatically as you add records.
