How to Repeat Excel Cell Values for Each Related Record in WPS Spreadsheets
Question details
The user needs to reshape a WPS Spreadsheets table so that a leftmost column value appears on every row associated with that value, using either dynamic-array formulas or Power Query.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A source table has grouped values in the leftmost column and related records in adjacent columns or rows; the user wants a flat row-by-row layout for reporting, analysis, or export.
- Observed behavior
- In the current layout, each leftmost value is shown once per group, so related records do not carry the grouping value on their own row.
Confirm that the source range is continuous and that each group value is present in the leftmost column. If you plan to use Power Query, click inside the source range and press Ctrl+T to format it as a table before loading it.
Repeat values with REDUCE, VSTACK, HSTACK, EXPAND, and SORT
Use this dynamic-array formula approach when your dataset is small enough to manage named ranges and you want a formula-driven result.
This method uses WPS Spreadsheets dynamic-array functions to combine repeated leftmost values with their related records. The result spills into the worksheet automatically.
Click the top-left cell where the reshaped table should appear.
Go to Formulas > Name Manager and create names for the leftmost label range, the related data range, and the output headers, for example letters, data, and labels.
Type =DROP(REDUCE("",letters,LAMBDA(a,v,LET(val,XLOOKUP(v,letters,data),SORT(VSTACK(a,HSTACK(labels,EXPAND(v,ROWS(val),,v),val)),{1,2})))),1) and press Enter.
If the spill range shows only part of the data, make sure each named range includes every source row and column, then press Enter again.
Refresh a Power Query output table for large datasets
Use this method when you have many records or need a refreshable result instead of recalculating formulas.
Reshape Grouped Records in WPS Spreadsheets
Use WPS Spreadsheets to repeat leftmost column values across each related record with dynamic-array formulas or a refreshable Power Query table, without retyping data.
- 1. Open the workbook in WPS Spreadsheets: Download WPS Office, launch WPS Spreadsheets, and open the Excel-compatible workbook that contains the grouped table.
- 2. Prepare the source range: Select the source data and press Ctrl+T to convert it into a table, or confirm that each group value is in the leftmost column.
- 3. Reshape the data: Use the Data tab to load the table into Power Query, or enter the dynamic-array formula shown above to repeat each leftmost value for its related records.
- 4. Load and refresh the result: Place the reshaped table in a worksheet, then right-click it and choose Refresh when the source data changes.
Frequently Asked Questions
Can I repeat leftmost column values without using Power Query in WPS Spreadsheets?
Yes. Use the dynamic-array formula with REDUCE, VSTACK, HSTACK, EXPAND, and SORT. Define the leftmost label range, related data range, and output headers, then enter the formula in the result cell and press Enter.
Why does my WPS Spreadsheets formula return #NAME? when I use REDUCE or VSTACK?
These functions require a current WPS Spreadsheets version that supports dynamic arrays. Check for office updates, and if the functions remain unavailable, use the Power Query method instead.
How do I update the reshaped table after I add more source rows?
If you used Power Query, right-click any cell in the output table and select Refresh. If you used a formula, make sure the named ranges or references include the new source rows.
Can I keep the repeated labels sorted in the reshaped output?
Yes. The formula example uses SORT to arrange output rows, and in Power Query you can select a column and use Home > Sort Ascending before loading the table.
Does WPS Spreadsheets support opening Microsoft Excel files with this type of table?
Yes. WPS Spreadsheets opens and saves .xlsx workbooks, including tables used by Power Query or dynamic-array formulas, while preserving the data structure.
