WPS Spreadsheet: Automatically Number Cartons with SCAN and SEQUENCE
Question details
The user needs a dynamic formula that assigns sequential carton numbers based on ordered quantities.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A carton quantity range must be converted into comma-separated carton numbers, with numbering beginning at carton 1 for the first active row.
- Observed behavior
- Using the wrong SCAN initial value causes the first carton sequence to begin at carton 2 instead of carton 1.
Confirm that your WPS Spreadsheet version supports LET, LAMBDA, SCAN, SEQUENCE, TEXTJOIN, and TEXTAFTER. Also verify that the carton quantity cells are defined as a named range called boxes.
Set SCAN's initial value to zero for carton numbering
Use this dynamic formula when carton quantities are stored in the named range boxes and the first carton must start at 1.
The initial value supplied to SCAN becomes the starting carton counter. Setting it to 0 makes the first nonblank quantity generate carton 1, while later rows continue from the last carton number.
Open the WPS Spreadsheet worksheet and click the first cell where the carton-number results should appear.
Paste the following formula into the formula bar, then press Enter: =LET(carton_numbering,LAMBDA(a,v,IF(v="",TEXTAFTER(a,",",-1,,,a),TEXTJOIN(",",,SEQUENCE(v,,a+1)))),SCAN(0,boxes,carton_numbering))
If boxes does not exist, select the quantity range, open the Formulas tab, choose Define Name, enter boxes as the name, and confirm the range.
Check the returned carton sequences in the worksheet. Blank quantity rows retain the last carton number, while active rows generate the next sequence.
Adjust the formula for a different starting carton number
Use this option when carton numbering should begin at a value other than 1.
Generate Dynamic Carton Numbers in WPS Spreadsheet
WPS Spreadsheet can create the carton-numbering formula directly using LET, LAMBDA, SCAN, SEQUENCE, TEXTJOIN, and TEXTAFTER. Define the quantity range as boxes, use zero as the SCAN seed, and let the dynamic array spill the results automatically.
- 1. Open WPS Spreadsheet: Launch WPS Office, open your workbook, and select the worksheet containing the carton quantities.
- 2. Define the quantity range: Select the quantity cells, open Formulas > Define Name, and name the range boxes.
- 3. Enter the formula: Click the output cell and enter the LET and SCAN formula with SCAN(0,boxes,carton_numbering).
- 4. Confirm the results: Press Enter and review the spilled carton-number sequences generated from the quantity range.
Frequently Asked Questions
Why does the first carton start at 2 instead of 1?
The SCAN seed controls the starting counter. Replace the initial value with 0, using SCAN(0,boxes,carton_numbering), so the first quantity generates carton 1.
What is the boxes reference in this formula?
boxes is a named range for the carton quantity cells. Select the quantity range, open Formulas > Define Name, enter boxes, and confirm the range.
Can blank quantity rows keep the previous carton number?
Yes. The IF(v="",...) portion detects blank cells and uses TEXTAFTER to extract the last carton number from the previous result.
Can I start carton numbering at a number other than 1?
Yes. Change the SCAN seed to the number immediately before the desired starting carton. For example, use SCAN(9,boxes,carton_numbering) to begin the first generated sequence at carton 10.
Does the formula update when carton quantities change?
Yes. Because SCAN and SEQUENCE create dynamic results, changing a value in the boxes named range recalculates the corresponding carton-number output.
