How to Repeat a Vertical Excel Sequence in a Spilled Array in WPS Spreadsheets
Question details
The user needs WPS Spreadsheets formulas to repeat the values in A2:A3 a specified number of times and return a vertical spilled array without manually copying formulas.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a vertical sequence in A2:A3 and a repetition count in B2, and wants the repeated sequence to spill dynamically into one column.
- Observed behavior
- The desired result is a vertical dynamic array such as 1, 15, 1, 15 that updates when the repetition count or source values change.
Make sure you are using a version of WPS Office that supports dynamic array functions such as TOCOL, SEQUENCE, MAKEARRAY, and LAMBDA. Place the repetition count in a single cell such as B2 and leave enough empty cells below the formula for the spill range.
Use TOCOL with SEQUENCE to repeat a vertical sequence
This is the most compact method when you want one dynamic formula that returns the repeated vertical sequence and updates automatically.
The formula =TOCOL(IF(SEQUENCE(,B2),A2:A3),,1) creates a 2-row by B2-column array of the original values and scans it column-by-column into a single vertical spill. This avoids manual copying and handles changes to A2:A3 or B2.
Type the number of repetitions in cell B2, for example 2.
Click the cell where the vertical list should begin, such as C2.
Type =TOCOL(IF(SEQUENCE(,B2),A2:A3),,1) in the formula bar and press Enter. WPS Spreadsheets spills the repeated sequence down the column automatically.
Use MAKEARRAY with LAMBDA and INDEX as an alternative
Use this formula if you prefer row-by-row construction or need to control the order through row indexing.
Repeat Excel Sequences Automatically with WPS Spreadsheets
WPS Spreadsheets supports modern dynamic array functions such as TOCOL, SEQUENCE, MAKEARRAY, and LAMBDA, so you can repeat a vertical sequence in a spilled array without copying formulas.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the values you want to repeat.
- 2. Click the output cell: Select a blank cell such as C2 where the spilled sequence should begin.
- 3. Type the dynamic array formula: Enter =TOCOL(IF(SEQUENCE(,B2),A2:A3),,1) in the formula bar and press Enter.
- 4. Adjust the repetition count: Change B2 to increase or decrease the number of repetitions; the spill expands or shrinks automatically.
Frequently Asked Questions
Can I repeat more than two values with the TOCOL method?
Yes. Replace A2:A3 with a larger range such as A2:A10. The TOCOL, IF, and SEQUENCE combination repeats each row in the selected range in order.
Why do I get a #SPILL! error after entering the formula?
The #SPILL! error means the spill range is blocked by existing data. Clear the cells below and to the side of the formula cell, or move the formula to a location with enough empty space for the full result.
Does this dynamic array method work in WPS Office as it does in Excel?
Yes. WPS Spreadsheets supports dynamic array functions such as TOCOL, SEQUENCE, MAKEARRAY, and LAMBDA in compatible versions. The workbook remains compatible with .xlsx format.
Can I repeat a horizontal sequence instead of a vertical one?
Yes. You can adjust the formula orientation using TOROW instead of TOCOL and change the SEQUENCE arguments to build a horizontal spilled result.
