How to Repeat Excel Data Based on a Count in Another Cell
Question details
The user wants to generate a detailed data list in Excel where each code or value is duplicated in rows according to a specific number defined in a neighboring cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Expanding a summary list of items and their associated quantities into a detailed list with one row per individual item.
- Observed behavior
- The goal is to automatically duplicate data across multiple destination rows based on a numerical value in an adjacent column without manually copying and pasting.
Ensure your source data is formatted without blank rows, with the item code in the first column and the numerical repetition count in the adjacent column. Save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve the VBA code.
Use a VBA Macro to Loop and Repeat Row Data
This method uses a VBA script to iterate through your source data, read the specified quantity, and output the item code multiple times into a destination sheet.
By utilizing the Visual Basic for Applications (VBA) editor, you can automate the process of reading a count value and duplicating the corresponding label. This macro reads the code from the first cell and the repetition count from the second cell of each row. It then writes the code to a secondary worksheet, moving down one row for every repetition.
Press the ALT + F11 keys simultaneously in your Excel workbook to open the Microsoft Visual Basic for Applications window.
In the top menu, click on 'Insert' and select 'Module' to create a blank workspace for your code.
Copy and paste the following code into the module window: Sub Test() Dim Source As Range, Dest As Range Dim i As Long Set Dest = Worksheets(2).Range("A1") For Each Source In Range("A1").CurrentRegion.Rows For i = 1 To Source.Cells(2).Value Dest.Value = Source.Cells(1).Value Set Dest = Dest.Offset(1) Next i Next Source End Sub
If your destination sheet is not the second sheet in your workbook, change 'Worksheets(2)' in the code to match the correct sheet index or name (e.g., Worksheets("Output")).
Close the VBA editor and return to your main sheet where the data starts at A1. Press ALT + F8, select 'Test' from the Macro dialog box, and click 'Run'.
Use WPS Spreadsheet to Run VBA Macros Easily
WPS Spreadsheet fully supports VBA macros, allowing you to easily run scripts to repeat cell data based on adjacent counts. It offers high compatibility with Excel (.xlsm) files and is highly efficient.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your summarized item lists.
- 2. Access the Developer Tools: Navigate to the 'Tools' tab and click on 'Developer' or 'Macros' to access the built-in VBA editor.
- 3. Insert the Code: Click 'Visual Basic', insert a new Module, and paste the repeating-row macro code provided in the solution above.
- 4. Execute the Macro: Run the code directly from the WPS editor to automatically generate your expanded list on the destination sheet.
Frequently Asked Questions
Can I repeat rows based on a count using formulas instead of VBA?
Yes, in newer versions of Excel you can use advanced formulas combining REPT, TEXTJOIN, and TEXTSPLIT, or utilize Power Query to unpivot and duplicate data. However, for older versions or very large datasets, a VBA macro is often the most reliable and flexible approach.
How do I enable the Developer tab to use macros?
Go to File > Options > Customize Ribbon, and check the box next to 'Developer' in the right pane. Click OK. This will add the Developer tab to your ribbon where you can access the Visual Basic editor.
Why is my VBA macro giving an 'Subscript out of range' error?
This usually happens if the destination sheet specified in the code (like Worksheets(2)) does not exist. Ensure your workbook has at least two sheets, or update the sheet reference in the code to match an existing sheet name.
Will this macro overwrite existing data on my destination sheet?
Yes, the provided macro starts writing at cell A1 of the destination sheet. If there is existing data there, it will be overwritten. You can modify the code to find the last empty row if you wish to append data instead.
