How to Generate Sequential Numbers with Alternating Suffixes in Excel
Question details
The user wants to automatically generate a sequence of numbers where each number is duplicated with alternating suffixes, such as 'A' and 'B' (e.g., 19-001A, 19-001B, 19-002A, 19-002B).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating specific project identifiers, inventory tags, or dual tracking codes in bulk without manual data entry.
- Observed behavior
- Instead of typing each identifier one by one, the user seeks a dynamic array formula to generate the full sequence automatically in a single column.
Ensure you are using a version of Excel that supports dynamic array formulas, such as Microsoft 365 or Excel 2021, as the SEQUENCE and TOCOL functions are required for this method.
Use TOCOL and SEQUENCE with an Array Constant
Combine the TOCOL, SEQUENCE, and TEXT functions with an array constant to quickly generate an alternating suffix list in a single column.
This formula works by generating a numerical sequence, formatting it with leading zeros and a prefix using the TEXT function, appending the 'A' and 'B' suffixes, and finally flattening the result into a single column using TOCOL.
Click on the cell where you want the first sequential identifier to appear.
Type the formula: =TOCOL(19&TEXT(SEQUENCE(999),"-000")&{"A","B"})
Press Enter. The dynamic array will automatically spill down the column, generating 19-001A, 19-001B, 19-002A, and so forth.
Generate Suffixes with a Custom Starting Value
Use this alternative formula if you want to define a specific starting sequence number directly within the SEQUENCE function.
Use WPS Spreadsheet to Handle Dynamic Arrays and Sequences
WPS Spreadsheet provides excellent support for advanced data generation and dynamic arrays. You can easily create complex tracking codes, format sequential numbers, and manage large datasets efficiently using standard formulas.
- 1. Open your worksheet: Launch WPS Spreadsheet and select an empty cell where you want your sequence to begin.
- 2. Input the sequence formula: Type your formula combining the TEXT, SEQUENCE, and array features to define your prefix and suffixes.
- 3. Generate the data: Press Enter and let WPS Spreadsheet dynamically populate the rows with your customized tracking identifiers.
Frequently Asked Questions
Why am I getting a #NAME? error when using TOCOL or SEQUENCE?
This error occurs if your version of Excel does not support dynamic array functions. You need Microsoft 365, Excel 2021, or a modern spreadsheet tool like WPS Spreadsheet that supports these advanced functions.
How can I change the suffixes to something other than A and B?
Simply modify the array constant {"A","B"} in the formula to your desired text strings, such as {"X","Y"} or {"Morning","Evening"}.
How do I make the sequence spill horizontally instead of vertically?
To generate the sequence across columns in a single row, replace the TOCOL function with the TOROW function in your formula.
Can I use HSTACK instead of the bracket array constant?
Yes, you can substitute {"A","B"} with HSTACK("A","B") if you prefer using a function over standard array notation. This is particularly useful when combining dynamic cell references instead of hardcoded text.
