Formula to Duplicate Entire Rows Based on Cell Value

Occasional Contributor

Hello everyone!


I'm trying to create a formula that will duplicate an entire row based on a cell's value. Below is a mock photo of what I am hoping to accomplish:


Screenshot 2022-03-28 114222.png


The formula would go in cell D2 and would duplicate all of column A and B based on the values in column B: i.e. Company 1 gets duplicated twice, Company 2 gets duplicated five times, etc. I have thousands of rows to do this for and it would take too long to do it by hand, especially when the values in column B get to be pretty large. The actual data is a little more complex than this, but any help would be greatly appreciated! Thanks in advance

7 Replies
best response confirmed by Corder (Occasional Contributor)


Sub companyemployees()

Dim i As Double
Dim j As Double
Dim z As Double
Dim w As Double

For i = 2 To 1000

j = Cells(i, 2).Value

For z = 2 To j + 1

Cells(z + w, 4).Value = Cells(i, 1).Value
Cells(z + w, 5).Value = Cells(i, 2).Value

Next z
w = w + z - 2

Next i

End Sub


Maybe with these lines of VBA code. Click the button in cell G2 in the attached file to start the macro.

Amazing! Would this still work when trying to have the results appear on a separate sheet? I'm still new to VBA in Excel; is this SharePoint compatible? The document this is on is shared Teams Group and needs to be accessible for everyone.


The results can appear on a different sheet. Click the button in cell G2 in the attached file and check the results in sheet "Tabelle2". I don't work with Sharepoint and can't answer this question unfortunately.


If you are using beta version, you may write the formula as below with 1 helper formula behind.





No worries,
I appreciate the help greatly!
Unfortunately, I don't have access to the beta since it is supplied by my company. I don't mind using a helper column since this sheet won't be seen anyway. Do you know of another formula or set of formulas that will accomplish the same thing using the non-beta version of Excel?



This is the set of formulas I ended up using for anyone who stubbles upon this thread later on. I have all of this on a helper sheet that I just hide and use sort and filter to call it on a different sheet. Thank you to everyone who helped!