Formula to Duplicate Entire Rows Based on Cell Value

[Corder]

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:

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

[#2 · Accepted solution]

@Corder 

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.

[#3]

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.

[#4]

@Corder 

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.

[#5]

@Corder 

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

[#6]

No worries,
I appreciate the help greatly!

[#7]

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?

[#8]

@Corder 

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!