SOLVED

# Formula to Duplicate Entire Rows Based on Cell Value

Occasional Contributor

# Formula to Duplicate Entire Rows Based on Cell Value

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

7 Replies
best response confirmed by Corder (Occasional Contributor)
Solution

# Re: Formula to Duplicate Entire Rows Based on Cell Value

``````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.

# Re: Formula to Duplicate Entire Rows Based on Cell Value

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.

# Re: Formula to Duplicate Entire Rows Based on Cell Value

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.

# Re: Formula to Duplicate Entire Rows Based on Cell Value

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

# Re: Formula to Duplicate Entire Rows Based on Cell Value

No worries,
I appreciate the help greatly!

# Re: Formula to Duplicate Entire Rows Based on Cell Value

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?

# Re: Formula to Duplicate Entire Rows Based on Cell Value

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!