Create Sequential Number Lists from Quantities in WPS Spreadsheets
Question details
The user wants to generate sequential number lists or ranges from a column of quantities in a spreadsheet, such as turning 1, 3, and 5 into 1, 2-4, and 5-9.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has quantities in one column and needs to create corresponding sequential number lists without typing them manually.
- Observed behavior
- The desired result is a formula-driven sequence that updates when the quantity column changes.
Make sure your quantities are in a single contiguous column with no blank rows in the data range. Also confirm that your WPS Office version supports the functions used in the formula, such as TEXTJOIN and ROW.
Use a helper-column formula to generate comma-separated sequences
Use this method in any WPS Spreadsheets version that supports TEXTJOIN; it keeps the logic simple and easy to audit.
This approach calculates the starting number for each row using the previous quantities, then creates each sequence with ROW and INDIRECT.
Select cell B2 next to the first quantity in column A, type =IF(A2="","",1), and press Enter. This gives the starting number for the first quantity.
In cell B3, enter =IF(A3="","",B2+A2) and fill down next to your quantity list. This stores the start number for every row.
In cell C2, enter =IF(A2="","",TEXTJOIN(",",TRUE,ROW(INDIRECT("1:"&A2))+B2-1)) and fill down. It returns a comma-separated number list such as 2,3,4.
Copy column C and use Paste Values if you need static lists, or keep the formulas so they update automatically when quantities change.
Use a single dynamic-array formula with LET and BYROW
Use this method in WPS Office versions that support dynamic array functions; it returns the entire result range at once.
Generate Sequential Number Lists from Quantities in WPS Spreadsheets
Use WPS Spreadsheets to build formula-driven number sequences from a quantity column, with full compatibility with Excel workbooks.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx file containing the quantity column, and enable editing if prompted.
- 2. Add the helper columns: Insert two blank columns next to your quantity column for the starting value and the generated sequence.
- 3. Enter the sequence formulas: Use the helper-column formulas from the recommended solution, starting in row 2 and filling down through the data.
- 4. Check and save the results: Verify that the generated comma-separated sequences match your quantities, then save the workbook as .xlsx to keep Excel compatibility.
Frequently Asked Questions
Can I make the formula show 2-4 instead of 2,3,4?
Yes. For a contiguous range, create the start and end values and join them: =B2&"-"&B2+A2-1. This returns 2-4 when the start is 2 and the quantity is 3.
What if my version of WPS Spreadsheets does not have TEXTJOIN?
Update WPS Office to the latest version. If TEXTJOIN is still unavailable, you can build the sequence with helper cells or use CONCATENATE, but the TEXTJOIN-based helper-column method is the easiest.
How do I apply this formula to hundreds of rows?
After entering the formulas in row 2, double-click the fill handle or drag the formulas down to the last quantity row. WPS Spreadsheets adjusts ROW and cell references automatically.
Will this method keep Excel compatibility when I send the file?
Yes. The helper-column formulas use standard spreadsheet functions and save in .xlsx format, so Microsoft Excel can open and recalculate the workbook normally.
