WPS Spreadsheet: Stack Product Rows into Dynamic Date, Quantity, and Package Columns
Question details
Create a dynamic formula that converts product data spread across multiple source rows into Date, Quantity, and Package output columns.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The source product table receives new records over time, and the reshaped output must include those records automatically.
- Observed behavior
- The desired result is a spill-range table with separate Date, Quantity, and Package columns that updates when source data is added.
Arrange the source dates and the associated Quantity and Package values in consistent ranges, with no unrelated cells inside the source data. If possible, convert the source range to a table so its structured references expand when new records are entered.
Use LET, TOCOL, WRAPROWS, and HSTACK to reshape the product data
Use this method when each date needs to be repeated for a fixed number of product entries and the Quantity and Package values should be returned as two columns.
Enter the formula in an empty area with enough space for the spilled results. Replace the named ranges dates and data with your own table columns or named ranges. The example repeats each date three times; change the three 1 values if each date has a different fixed number of product entries.
Select the source range and use Insert > Table, then assign clear column names such as Date, Quantity, and Package. Alternatively, create named ranges for the date and product-data ranges.
Click the first blank cell where the Date, Quantity, and Package results should begin. Ensure the cells below and to the right are empty so the formula can spill.
Type a formula such as =LET(ext_dates,TOCOL(dates*{1,1,1}),wrapped,WRAPROWS(TOCOL(data),2),HSTACK(ext_dates,wrapped)) and press Enter. Replace dates with the date range and data with the combined Quantity-and-Package source range.
In dates*{1,1,1}, use one 1 for each product row that belongs to a date. In WRAPROWS(TOCOL(data),2), keep 2 for Quantity and Package pairs, or change the value if each output record contains a different number of fields.
Enter new records in the next row of the source table. When structured table references are used, the formula output expands automatically.
Use structured table references to include new product records automatically
Use this setup when the formula works initially but does not include rows added below a fixed source range.
Reshape Product Records Automatically with WPS Spreadsheet
WPS Spreadsheet lets you build spill formulas that transform repeating product records into clean Date, Quantity, and Package columns. Combine dynamic array functions with table references so the report updates as your source list grows.
- 1. Open the source workbook: In WPS Office, open the workbook and select the worksheet containing the product source data.
- 2. Format the data as a table: Select the source range and choose Insert > Table so newly entered rows are included in the source structure.
- 3. Choose an output location: Click a blank cell outside the table where the reshaped three-column result can spill.
- 4. Build the dynamic formula: Enter a LET formula that uses TOCOL to flatten values, WRAPROWS to pair Quantity and Package values, and HSTACK to add the repeated Date column.
- 5. Verify the dynamic result: Add a test product record to the table and confirm that the spilled output refreshes with its Date, Quantity, and Package values.
Frequently Asked Questions
Why does the formula repeat each date three times?
The expression dates*{1,1,1} creates three copies of every date. Use the same number of 1 values as the number of product entries associated with each date.
How do I change the output from Quantity and Package to three product fields?
Add the third field to the flattened data source and change WRAPROWS(TOCOL(data),2) to WRAPROWS(TOCOL(data),3). The number must match the number of values in each output record.
Why is my dynamic formula showing a spill error?
Clear any values, merged cells, or other content blocking the cells where the result needs to expand. Then re-enter the formula in the top-left output cell.
Will new records be included without editing the formula?
Yes, when the formula uses structured references from an expanding table. Add complete records directly below the table, and its referenced columns will grow automatically.
Can I use this formula when each date has a different number of product rows?
Not with a fixed repeat expression such as {1,1,1}. For variable product counts, first create a helper structure that associates each product row with its date, then stack the aligned columns.
