How to Repeat Excel Values Based on a Quantity in Another Sheet
Question details
The user wants to automatically generate a list in a second sheet where each item from a source sheet is repeated a specific number of times based on its corresponding quantity value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an expanded list (e.g., for individual item tracking, labels, or flat file exports) from a consolidated inventory table.
- Observed behavior
- The user needs a dynamic solution that automatically updates and repeats products according to source quantities whenever the original data changes, avoiding manual copying and pasting.
Ensure your source data is formatted as an official Table (select your data and press Ctrl+T) so that any future additions or changes to quantities will be dynamically included in your expanded list.
Expand Rows Automatically Using Power Query
Power Query is the most robust, macro-free method to duplicate rows based on a quantity column and easily refresh when data changes.
By utilizing Power Query, you can transform a single row containing a quantity into multiple individual rows. This creates a clean, automated pipeline between your source data and your expanded output sheet.
Select any cell in your source table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, go to the Add Column tab and click 'Custom Column'. Name it 'Repeat' and enter the formula ={1..[Quantity]} to generate a list of numbers for each row.
Click the expand icon (two diverging arrows) at the top right of your new 'Repeat' column header, then select 'Expand to New Rows'. Your items will now be duplicated based on the quantity.
Right-click the 'Repeat' column and select 'Remove' since it is no longer needed. Finally, go to the Home tab and click 'Close & Load' to output the expanded list to a new worksheet.
Whenever you update the quantities in your original source table, simply go to the Data tab and click 'Refresh All' to instantly update your expanded list.
Duplicate Values Using Dynamic Array Formulas
If you prefer a formula-based approach without using Power Query, you can use modern dynamic array functions to spill the repeated values automatically.
Easily Manage and Expand Data with WPS Spreadsheet
WPS Spreadsheet offers powerful data handling tools, including advanced formulas and seamless data transformation features, helping you automatically duplicate rows based on quantities with ease.
- 1. Open Your Source Data: Launch WPS Spreadsheet and open the workbook containing your product and quantity list.
- 2. Format as a Table: Select your dataset and format it as a Table to ensure your data ranges remain dynamic for future updates.
- 3. Apply Expansion Methods: Use dynamic array formulas to effortlessly repeat your items according to the quantities specified in your adjacent columns.
- 4. Save and Share: Save your finalized workbook in the standard .xlsx format to maintain perfect compatibility with colleagues using other Office suites.
Frequently Asked Questions
Can I repeat rows based on quantity without using VBA macros?
Yes. You can easily achieve this using Power Query to expand a custom list, or by utilizing modern dynamic array formulas. Neither of these methods requires you to write or run VBA macro code.
How do I update the repeated list if my source quantities change?
If you used the Power Query method, simply navigate to the Data tab on your ribbon and click 'Refresh All'. The expanded list will immediately recalculate to reflect any new or modified quantities.
Why does my dynamic array formula show a #SPILL! error?
A #SPILL! error occurs when the formula does not have enough empty cells below or beside it to display all the repeated values. Ensure you clear any existing data or spaces that are blocking the destination range.
Will my Power Query solution work if a product's quantity is zero?
If a product's quantity is zero, the formula ={1..[Quantity]} in Power Query may cause an error. To prevent this, you can filter out rows with a zero quantity before adding the custom column, or use an IF statement in your custom column formula.
