Function Problems

How to Repeat Excel Names by Quantity Without VBA

Steven Parker Oct 21, 2026 868 views

Question details

The user needs to repeat station names based on corresponding quantity values while excluding blanks and zeros, using only formulas instead of VBA.

How to Repeat Names by Quantity in Excel Without VBA

Product: Excel
Device & OS: not provided
Scenario: Duplicating rows based on a specific quantity column value where VBA macros cannot be used due to device limitations.
Observed behavior: Names are successfully repeated corresponding to the exact number in the quantity column, compiled into a single clean column with zero and blank values ignored.

Before you start

Before using dynamic arrays like TOCOL, ensure your spreadsheet software is updated to a version that supports modern dynamic array functions, and verify that your quantity column contains purely numeric values.

Solution 1Recommended

Use Dynamic Array Formulas (COUNTIF and TOCOL)

Use iterative calculation and dynamic array functions to generate and stack the repeated names into a single column automatically.

This method involves using a combination of COUNTIF to track the number of repetitions and TOCOL to condense the resulting matrix into a single column without blanks.

Enable Iterative Calculation

Go to File > Options > Formulas. Check the box for 'Enable iterative calculation' to allow the formulas to reference previous cells properly without errors.

Apply the Repetition Formula

Assuming names are in column A and quantities in column B, enter the formula =IF(COUNTIF($D1:D1,$A1)<$B1,$A1,"") in an adjacent range (e.g., E1) and drag it across columns to generate repeated names.

Consolidate to a Single Column

In a new cell, type =TOCOL(D1:G5, 3, FALSE) to convert the multi-column results into a single vertical list. The '3' parameter ensures blanks and errors are ignored.

Paste as Values

Select the final generated column, copy it (Ctrl+C), right-click the destination cell, and choose 'Paste as Values' to remove the underlying formulas.

Use Dynamic Array Formulas (COUNTIF and TOCOL)

No VBA Required: This formula-based approach is completely safe from macro security blocks and works flawlessly on devices that restrict VBA.

Solution 2

Use Power Query to Expand Rows

Utilize Excel's built-in Power Query editor to duplicate rows according to a quantity column easily.

Advanced Spreadsheet Management

Process Complex Data Effortlessly with WPS Spreadsheet

WPS Office offers comprehensive dynamic array functions and robust formula support, allowing you to quickly reorganize and duplicate data based on specific conditions without relying on complicated VBA scripts.

1. Step 1: Open your dataset containing names and quantities in WPS Spreadsheet.
2. Step 2: Utilize standard lookup and count functions to organize your table dynamically.
3. Step 3: Easily consolidate and paste values using the intuitive right-click menu.

Fully compatible with Microsoft Excel formulas and data structures.Lightweight performance that runs smoothly without macro-related device issues.Rich set of built-in functions to handle complex data transformation seamlessly.

Frequently Asked Questions

Why does the TOCOL function return a #NAME? error?

The TOCOL function is a newer dynamic array formula. If you receive a #NAME? error, your current spreadsheet software version may not support it. In this case, you should update your software or use the Power Query alternative.

How can I make sure blank and zero quantities are ignored?

When using the TOCOL function, setting the second argument to '3' automatically ignores blanks and errors. In Power Query, rows with a zero or null quantity will automatically yield no expanded rows or can be filtered out before expanding.

Do I need to save my file in a special format for this to work?

No. Because these solutions only use built-in formulas and standard data tools, you can save your document as a standard .xlsx file. You do not need the macro-enabled (.xlsm) format since no VBA is used.

Excel & Data Problems