WPS Spreadsheet: Automatically Generate a Production List from Excel Table Values
Question details
The user needs to expand product data from an Excel-compatible table into a printable production list, repeating each product according to its quantity.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Product lengths, grades, and quantities are stored in a source table, and the user wants an automatically refreshed production list.
- Observed behavior
- A production-list output has not yet been generated from the source table values.
Create a normalized source table with one product per row and the columns Product, Length, Grade, and Quantity. Confirm that every Quantity value is a nonnegative number, then name the table ProductionTable from the Table tools or Name Manager.
Generate the repeated production list with a dynamic-array formula
Use this method when your WPS Spreadsheet version supports LET, FILTER, SCAN, SEQUENCE, XMATCH, INDEX, and HSTACK; the output updates when the source table changes.
The formula calculates cumulative quantity totals, creates one position for every production unit, and maps each position back to the corresponding product row. Place the formula in the top-left cell of an empty output area so the results can spill automatically.
If your version does not support one of these dynamic-array functions, use a helper-column design or a macro-enabled workflow instead of replacing the source table with manually duplicated rows.
In WPS Spreadsheet, select the source range and choose Insert > Table. Set the table name to ProductionTable and use the headers Product, Length, Grade, and Quantity.
On a new worksheet, select cell A2 and enter =LET(data,FILTER(ProductionTable[[Product]:[Quantity]],ProductionTable[Quantity]>0),product,CHOOSECOLS(data,1),length,CHOOSECOLS(data,2),grade,CHOOSECOLS(data,3),qty,CHOOSECOLS(data,4),pos,SEQUENCE(SUM(qty)),ends,SCAN(0,qty,LAMBDA(a,v,a+v)),idx,XMATCH(pos,ends,1),HSTACK(INDEX(product,idx),INDEX(length,idx),INDEX(grade,idx))). Press Enter to spill the generated list.
In cells A1:C1, enter Product, Length, and Grade. If each production unit needs a sequence number, enter Unit No. in A1 and place =SEQUENCE(SUM(ProductionTable[Quantity])) in A2, then place the three-column formula in B2.
Select the spilled output range, use Home > Borders and the Number Format controls to make it readable, then choose Page Layout > Print Area > Set Print Area before selecting File > Print.
Build a Refreshable Production List in WPS Spreadsheet
WPS Spreadsheet can use an Excel-compatible table and dynamic-array formulas to repeat each product according to its quantity, preserve length and grade values, and produce a formatted list ready for printing.
- 1. Create the source table: Open WPS Spreadsheet, choose Insert > Table, and define the Product, Length, Grade, and Quantity columns.
- 2. Name the table: Use the Table tools or Formulas > Name Manager to name the table ProductionTable.
- 3. Insert the formula: Select an empty output cell and enter the LET, FILTER, SCAN, SEQUENCE, XMATCH, INDEX, and HSTACK formula to expand the quantities into individual production rows.
- 4. Format the output: Add headings, apply borders and number formats from the Home tab, and check the spilled rows for missing or invalid quantities.
- 5. Print the list: Use Page Layout > Print Area > Set Print Area, then select File > Print to produce the final production list.
Frequently Asked Questions
What columns are required to generate the production list?
Use one row per product with Product, Length, Grade, and Quantity columns. The formula filters out rows with zero or blank quantities and repeats the remaining products according to Quantity.
Why does the dynamic-array formula return a function error?
Your WPS Spreadsheet version may not support one of the newer functions, such as SCAN, XMATCH, HSTACK, or CHOOSECOLS. Update WPS Office or use a helper-column or macro-based method for older versions.
How do I prevent incorrect output when quantities are entered as text?
Select the Quantity column and choose Home > Number Format > Number. Replace text values with numeric values, remove extra spaces, and confirm that quantities are zero or greater.
Can the generated production list be printed as a numbered list?
Yes. Add a Unit No. heading and enter =SEQUENCE(SUM(ProductionTable[Quantity])) in the first output cell. Format the results through Home > Number Format and set the print area from Page Layout.
Will the list update after I add a new product row?
Yes, provided the new row is inside the named ProductionTable and its Quantity is numeric. The spilled formula recalculates when the table values change.
