How to Repeat Excel Table Rows Based on a Quantity Without VBA
Question details
The user needs to dynamically repeat records and generate sequential dates from a repeat-factor column without using VBA macros.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a budget template where payment records must recur sequentially for a specified number of months automatically.
- Observed behavior
- Requires a dynamic array formula solution to repeat rows based on a quantity column instead of manually copying rows or writing macros.
Ensure your source data is organized in a structured table and that all date values are formatted as real Excel dates rather than plain text.
Use TOCOL and SEQUENCE to Repeat Rows and Dates
This method uses dynamic array functions to generate repeated rows based on a quantity column and increments dates accordingly.
Combining TOCOL, IFS, and SEQUENCE provides a clean array-based approach to duplicate cell values exactly as many times as specified in a target column. The LET and EDATE functions are then used to step the dates forward sequentially.
To copy the names based on the quantity, enter the formula `=TOCOL(IFS(D2:D4>=SEQUENCE(,MAX(D2:D4)),A2:A4),2)`. Replace A2:A4 with your name range and D2:D4 with your quantity column range.
To generate the recurring dates, use the formula `=LET(cols,SEQUENCE(,MAX(D2:D4)),test,D2:D4>=cols,EDATE(TOCOL(IFS(test,B2:B4),2),TOCOL(IFS(test,cols-1),2)))`. Ensure B2:B4 points to your initial start dates.

Use REDUCE and LAMBDA for Advanced Row Expansion
For complex structured tables, combining REDUCE, LAMBDA, and VSTACK iterates through rows to stack repeated records together.
Use WPS Spreadsheet for Advanced Dynamic Array Formulas
WPS Spreadsheet fully supports advanced array functions like SEQUENCE, TOCOL, and LET, making it easy to repeat rows based on numeric values without writing any VBA code.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing your budget template.
- 2. Input Array Formulas: Select the target cell and input the SEQUENCE and TOCOL formulas to extract and repeat your row data.
- 3. Press Enter to Spill: Hit Enter and watch the formulas dynamically expand and repeat the required records downwards automatically.

Frequently Asked Questions
Why am I getting a #SPILL! error when using these formulas?
A #SPILL! error occurs when the dynamic array formula does not have enough empty cells to display all the generated rows. Clear the cells blocking the path below the formula to allow the results to populate.
Can I use these formulas in older versions of Excel?
No, functions like TOCOL, SEQUENCE, LET, and LAMBDA are part of the dynamic array updates available only in Microsoft 365, Excel 2021, and modern versions of WPS Spreadsheet.
How does the EDATE function help in this scenario?
The EDATE function adds a specific number of months to a given start date. In these dynamic formulas, it increments the date for each repeated row to create an accurate sequence of recurring monthly payments.
What if my quantity column contains a zero?
If the repeat factor (quantity) is zero, the formulas designed here may produce an error. Depending on your needs, you may want to wrap your formula in an IFERROR or use the FILTER function to remove rows with a zero quantity before processing them.




