Function Problems

How to Automatically Assign Excel Rows to People Based on Quantity

John Wilson Oct 9, 2026 869 views

Question details

The user wants to distribute a specific number of rows to individuals by automatically duplicating their names based on an adjacent quantity column, avoiding manual copying and pasting.

How to Assign Excel Rows to People Automatically Without Copying Names

Product: Excel
Device & OS: not provided
Scenario: Distributing tasks or rows dynamically by repeating a person's name according to a numerical value assigned to them in the dataset.
Observed behavior: A dynamic list needs to be generated where each name is expanded and duplicated the exact number of times specified by their assigned quantity.

Before you start

Ensure you are using a modern version of your spreadsheet software (like Microsoft 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions such as REDUCE, LAMBDA, and VSTACK.

Solution 1Recommended

Use REDUCE, LAMBDA, and VSTACK Formulas

Use advanced dynamic array functions to iterate over the data and expand each name dynamically based on its corresponding quantity.

This is the most robust method for dynamically spilling arrays in modern spreadsheet software. It takes advantage of the REDUCE function to loop through the quantities and VSTACK to stack the repeated names sequentially.

Organize your source data

Ensure your data is set up with the names in column A (e.g., A2:A4) and their corresponding assigned quantities in column B (e.g., B2:B4).

Select the output cell

Click on an empty cell where you want the new expanded list of assigned names to begin (for example, cell D2).

Enter the formula

Type the formula: =DROP(REDUCE("",A2:A4,LAMBDA(a,b,VSTACK(a,EXPAND(b,OFFSET(b,0,1,,1),,b)))),1) and press Enter. The list will automatically populate.

Dynamic Spilling: Because this relies on dynamic arrays, the resulting names will automatically "spill" down the column without you needing to drag the formula.

Solution 2

Use TEXTSPLIT and TEXTJOIN Formulas

An alternative method that converts the repeated names into a long text string and then splits them back into individual rows.

Master Advanced Data Assignment Easily with WPS Spreadsheet

WPS Office fully supports dynamic array functions like REDUCE, LAMBDA, VSTACK, and TEXTSPLIT, allowing you to instantly automate row assignments and complex data manipulations.

1. Download and open WPS Office: Launch WPS Spreadsheet and open your existing workbook containing the names and quantities.
2. Set up the formula: Select the destination cell for your dynamic list.
3. Apply the REDUCE array formula: Paste the provided dynamic array formula into the formula bar and press Enter to instantly spill the data into the rows.

Fully compatible with Microsoft Excel formulas (.xlsx)Supports modern dynamic array capabilities for automated listsFree to download with a lightweight, fast installationIntuitive and familiar interface for seamless spreadsheet management

Frequently Asked Questions

Why am I getting a #NAME? or #CALC! error when I enter the formula?

These errors usually occur if your spreadsheet software version does not support modern dynamic array functions like REDUCE, LAMBDA, or VSTACK. Make sure you are using an updated version of Excel or the latest version of WPS Office.

What happens if I change the quantity next to a person's name?

Since these are dynamic array formulas, any updates you make to the numbers in the source quantity column will automatically recalculate and adjust the length of the generated list in real-time.

Can I achieve this without using complex formulas?

Yes, you can also use Power Query. By loading your data into Power Query, adding a custom column using the formula '{1..[Quantity]}', and expanding that new column to new rows, you can achieve the exact same result through a visual interface.

How can I automatically add row numbers next to the generated names?

You can wrap your entire dynamic formula inside an HSTACK function along with a SEQUENCE function (e.g., =HSTACK(SEQUENCE(SUM(B2:B4)), [YourFormula])) to generate automatic numbering next to the assigned names.

Excel & Data Problems