How to Generate Order Numbers Ignoring Blank Rows in Excel
Question details
The user needs to generate sequential order numbers in a spreadsheet, ensuring that blank rows are ignored and do not disrupt the numbering sequence.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing order lists with multiple item rows and empty rows where a continuous order ID needs to be assigned without incrementing on blank spaces.
- Observed behavior
- Order numbers like 24-0001 and 24-0002 should correctly display on the first row of each new order, and any blank rows must not alter the overall sequence.
Ensure your version of Excel supports dynamic array functions (such as Microsoft 365 or the latest WPS Office), as the most efficient solution requires the LET, SCAN, and HSTACK functions.
Use Dynamic Array Functions (LET and SCAN) in Excel 365
Utilize modern dynamic array functions to systematically count new orders and item positions while completely skipping blank rows.
By utilizing the LET function, you can define variables within your formula to compare each row with the previous one. The SCAN function then iterates through these arrays to increment your order numbers only when a new, non-blank order is detected.
Click on the first cell of the column where you want the sequence of order numbers to begin.
Input the following formula into the formula bar: =LET(priorOrder, DROP(VSTACK("", order), -1), newOrder, order<>priorOrder, orderNum, SCAN(0, newOrder, LAMBDA(acc, new, IF(new, acc+1, acc))), itemNumber, SCAN(0, newOrder, LAMBDA(acc, new, IF(new, 1, acc+1))), HSTACK(orderNum, itemNumber))
Replace the word 'order' in the formula with the actual cell range of your source data (for example, A2:A100) to ensure the blank-row logic correctly matches your dataset.
Press the Enter key. The formula will automatically spill down the column, generating accurate order sequence numbers and item numbers within each order.
Use Standard IF and MAX Functions for Older Excel Versions
If you are using an older version of Excel that lacks dynamic arrays, you can use a combination of IF and MAX functions to achieve a similar result.
Easily Manage Advanced Array Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced dynamic array functions, making it simple to process complex datasets and generate sequential order numbers effortlessly.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open your workbook containing the order list.
- 2. Apply the array formula: Select the target cell, paste the LET and SCAN formula to generate the sequence, and press Enter.
- 3. Save your document: Press Ctrl+S to save your work. The document remains fully compatible with standard .xlsx formats.
Frequently Asked Questions
Why does my formula return a #NAME? error?
A #NAME? error usually occurs if your version of Excel does not support dynamic array functions like LET, SCAN, or HSTACK. You will need Microsoft 365, Office 2021, or the latest version of WPS Office to use these advanced functions.
Can I customize the order number format to include text, like '24-0001'?
Yes. You can modify the formula by wrapping the final 'orderNum' output in a TEXT function. For example, replacing 'orderNum' in the HSTACK portion with '"24-"&TEXT(orderNum, "0000")' will format your output exactly as '24-0001'.
Will this formula automatically update if I insert a new row?
Because dynamic array formulas automatically spill into adjacent empty cells, the results will update. However, if you insert a row directly into the middle of your source range, ensure your 'order' range reference covers the new row, or format your source data as an official Excel Table.
