Function Problems

How to Repeat Filtered Excel Values Based on a Quantity Column

Chanuka Geekiyanage Oct 14, 2026 871 views

Question details

The user needs to filter specific rows containing a target marker and repeat those filtered values dynamically according to an adjacent repeat-count column.

How to Repeat Filtered Excel Values Based on a Quantity Column

Product: Spreadsheets
Device & OS: not provided
Scenario: Structuring or expanding datasets where specific marked rows need to be duplicated multiple times based on a specified integer quantity.
Observed behavior: A single continuous column of repeated values is generated dynamically based on the criteria and quantity, avoiding manual copying and pasting.

Before you start

Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions such as FILTER, REDUCE, LAMBDA, and VSTACK.

Solution 1Recommended

Use REDUCE and LAMBDA for Dynamic Expansion

This single-cell formula dynamically filters marked rows, looks up their required repeat counts, and automatically stacks the repeated values into a single column.

By nesting functions like REDUCE, FILTER, and LAMBDA, you can create a loop-like behavior directly in a cell. This allows the spreadsheet to evaluate each filtered row and expand it based on the quantity specified in a separate column.

Select the target cell

Click on the cell (for example, E2) where you want the resulting list of repeated values to begin.

Enter the REDUCE formula

Assuming Column A holds the values, Column B contains your marker "x", and Column C holds the quantity, enter the following formula: =DROP(REDUCE("",FILTER(A2:A7,B2:B7="x"),LAMBDA(a,b,VSTACK(a,EXPAND(b,XLOOKUP(b,A2:A7,C2:C7),,b)))),1)

Execute the formula

Press the Enter key. The formula will automatically spill down the column, repeating each filtered value for the specified number of times.

Use REDUCE and LAMBDA for Dynamic Expansion

Dynamic Updates: As long as you use dynamic arrays, any changes made to the quantity column (Column C) or the marker column (Column B) will automatically update your final repeated list.

Solution 2

Use Helper Columns with TEXTJOIN and TOCOL

An alternative two-step approach that first generates a comma-separated list of values and then splits them into individual rows.

Advanced Data Processing

Easily Manage Dynamic Arrays with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions, making complex data manipulation tasks like expanding and stacking arrays straightforward and efficient.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing the data you need to filter and repeat.
2. Select the destination cell: Click on the empty cell where you want the resulting single-column list to appear.
3. Input the array formula: Paste the REDUCE and LAMBDA formula into the formula bar and adjust the range references to match your dataset.
4. Apply the calculation: Press Enter to instantly generate the spilled array of repeated values.

Full support for modern dynamic array functions including FILTER and SEQUENCEHighly compatible with Microsoft Excel formulas and file formatsFree, lightweight, and user-friendly interface for daily data processing tasksSeamless processing of complex nested LAMBDA calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning a #NAME? error?

A #NAME? error typically occurs if your current version of the spreadsheet software does not support newer dynamic array functions such as REDUCE, LAMBDA, or VSTACK. Check for software updates or consider using the helper column method.

Can I repeat values based on multiple conditions instead of just one marker?

Yes, you can modify the FILTER criteria in the formula. To check for multiple conditions, multiply them together inside the FILTER function, for example: FILTER(A2:A7,(B2:B7="x")*(C2:C7>2)).

What does the DROP function do in this specific formula?

The DROP function is used to remove the very first row of the generated array. Because the REDUCE function starts with an initial empty string (""), DROP(..., 1) ensures that this blank starter row is excluded from your final list.

Excel & Data Problems