Forum Discussion
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 🙂
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 SubMaybe with these lines of VBA code. Click the button in cell G2 in the attached file to start the macro.
25 Replies
The VBA provides me with a good start to what I am trying to do. What I am trying to do is duplicate records and replace a cell value.
My PRIMARY table has values in ColC which can vary:
My LOOKUP table lists FirstNames, a count, and then the variations:
I want to count rows in the PRIMARY sheet and store as a variable and then loop. The loop:
- In PRIMARY, store C2 value (here it is Robert)
- Go to LOOKUP table
- Compare C2 to each value in ColA. If no match, increment in PRIMARY table. Here C2 matches A7, so store each of the variable names in C7:G7 separately and then duplicate the PRIMARY table record A2:H2 five (5) times (per B7) at the end of the PRIMARY table.
Each of the five duplicates now have Robert as a ColC value.
VBA then increments through the new records and replaces Robert with the variable:
Can this be done? Any help would be appreciated.
BlueBenz
An alternative could be Power Query. In the attached file you can enter data in the blue dynamic tables in sheets "PRIMARY" and "LOOKUP". Then you can click in any cell in the green table and right-click with the mouse. Then select refresh.
If you are using beta version, you may write the formula as below with 1 helper formula behind.
Starrysky1988 Thank you for this example. The only problem is that Column D & E are bringing back numbers instead of my values from columns A. I think I am missing the formula that you have down in the step area? Also noticed it is throwing an error when I have a 1 in column B. Use case is I would like the email in column A repeated as many times as the value in column B.
I need a list of emails repeated as many times as in column B
LaurieBluegator With MS365, you could try something like this to generate a repeating list of email addresses from column A, based on the values in column B:
=LET(arr, A2:B6, REDUCE("Email", SEQUENCE(ROWS(arr)), LAMBDA(v,n, VSTACK(v, IF(SEQUENCE(INDEX(arr, n, 2)), INDEX(arr, n, 1))))))Or, if you also need the values in column B to repeat alongside the email addresses, try:
=LET(arr, A2:B6, REDUCE({"Email","Rows"}, SEQUENCE(ROWS(arr)), LAMBDA(v,n, VSTACK(v, IF(SEQUENCE(INDEX(arr, n, 2)), INDEX(arr, n, 0))))))Adjust the range reference (A2:B6) as needed.
UPDATE: simplified/optimized methods include...
=TOCOL(IFS(B2:B6>=SEQUENCE(, MAX(B2:B6)), A2:A6), 2)Or, to repeat both columns:
=CHOOSEROWS(A2:B6, TOCOL(IFS(B2:B6>=SEQUENCE(, MAX(B2:B6)), SEQUENCE(ROWS(A2:A6))), 2))Cheers! 🙂
- 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?
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 SubMaybe with these lines of VBA code. Click the button in cell G2 in the attached file to start the macro.
Hi All, the VBA table and code would work perfectly for my application of duplicating stock values which than need to be exported as csv. I am unfortunately very unfamiliar with VBA and am not sure how to extend the code to have the same function for Rows A to G, taking the copy value from Row D.
Any help in how to adjust this is greatly appreciated.
Sub product() Dim i As Double Dim j As Double Dim z As Double Dim k As Long Dim w As Double Range("K:Q").Clear k = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To k j = Cells(i, 4).Value For z = 2 To j + 1 Cells(z + w, 11).Value = Cells(i, 1).Value Cells(z + w, 12).Value = Cells(i, 2).Value Cells(z + w, 13).Value = Cells(i, 3).Value Cells(z + w, 14).Value = Cells(i, 4).Value Cells(z + w, 15).Value = Cells(i, 5).Value Cells(z + w, 16).Value = Cells(i, 6).Value Cells(z + w, 17).Value = Cells(i, 7).Value Next z w = w + z - 2 Next i End SubIn the attached file in sheet "Tabelle2" you can click the button in cell I2 to run the macro that returns the result in columns K to Q.
- 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.
