Function Problems

How to Repeat Excel Table Rows Based on a Quantity Without VBA

Tauseeq MagsiTauseeq Magsi Nov 11, 2026 870 views

Question details

The user needs to dynamically repeat records and generate sequential dates from a repeat-factor column without using VBA macros.

Repeat Excel Table Rows Based on a Quantity Without VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Repeat the text or name records

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.

2
Generate sequential start dates

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 TOCOL and SEQUENCE to Repeat Rows and Dates
Dynamic Array Spilling: These formulas will automatically spill downward. Ensure you have enough empty rows below your formula cell to avoid a #SPILL! error.
Advanced Data Processing

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. 1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing your budget template.
  2. 2. Input Array Formulas: Select the target cell and input the SEQUENCE and TOCOL formulas to extract and repeat your row data.
  3. 3. Press Enter to Spill: Hit Enter and watch the formulas dynamically expand and repeat the required records downwards automatically.
Fully compatible with Microsoft Excel formulas and array functionsFree, lightweight, and fast spreadsheet processingSeamlessly handles dynamic arrays and spill rangesClean, tabbed interface for efficient data management
QA img-9

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.