Mar 28 2022 08:47 AM - last edited on Nov 09 2023 11:10 AM by
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 🙂
Mar 28 2022 09:15 AM
SolutionSub 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.
Mar 28 2022 09:33 AM
Mar 28 2022 09:45 AM
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.
Mar 28 2022 10:14 AM
If you are using beta version, you may write the formula as below with 1 helper formula behind.
Mar 29 2022 05:26 AM
Mar 29 2022 05:36 AM
Mar 30 2022 09:49 AM
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!
Nov 18 2022 03:01 PM
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:
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
Nov 19 2022 05:37 AM
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.
Nov 19 2022 09:24 AM
This is very close to what I am seeking. I was looking for VBA to add to existing coding I have. The Power Query [PQ] concept you have does make it a good possibility. Permit me to further explain.
My workbook starts with two worksheets: PRIMARY and LOOKUP. PRIMARY has the basic information on the left and is manually input. To the right of the PRIMARY data are several additional columns which are populated by VBA which takes the values in Cols B, C & D to make cell values "Ln, Fn Mn", "Ln, Fn Mi", "Fn Mn Ln", "Fn Mi Ln", "Fn Ln." This coding is done and functions. What I seek is to append the FirstName variations to the existing PRIMARY table before I run the VBA which populates cells to the right of the PRIMARY range, creates additional tabs (e.g., “Ln, Fn Mn”, etc.), exports the tab data to delimited text files, and then deletes the new tabs then return to just having PRIMARY and LOOKUP tabs.
The Power Query [PQ] as it is now has the REFRESH table below and it includes the original record and the variations. It would be much better if:
My PRIMARY has some cells in color which would be copied between worksheets, so the alternating row color should not be present.
I hope this better explains what I am looking for. My problem is working with FOR/NEXT and LOOPING which I know is an important part . . . I just don’t quite have it under control. I am not familiar with Power queries, i.e., where they are coded, but with your example will try to understand and adapt.
If this can be done with VBA, much better, but any ideas will help.
Nov 21 2022 10:41 AM
Certainly you can output the PQ result on a different worksheet named e.g. "VARIATIONS". Instead of a click button you can refresh the query with Alt+F5. Unfortunately i can't help you with the other tasks. Maybe you want to start a new discussion and ask the experts of the community for possible solutions.
Nov 05 2023 01:57 PM
@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
Nov 06 2023 12:10 AM - edited Jul 24 2024 09:00 PM
@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! 🙂
Nov 09 2023 10:37 AM
@djclements Thank you so much. I appreciate the options. I will give these a try.
Apr 03 2024 06:40 PM
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.
Apr 04 2024 01:39 AM
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 Sub
In 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.
Apr 22 2024 07:12 PM
Jul 23 2024 05:26 AM
Good day!
Hi! @OliverScheurich and all,
Can you please help me on my macro excel for entire rows. Below is a mock photo of what I am hoping to accomplish: Thank you!
Jul 23 2024 10:24 AM
Sub net()
Dim i As Long, j As Long, k As Long, l As Long
Range("X3:Z1048576").Clear
l = 3
For j = 2 To 6
For i = 3 To 5
For k = 1 To Cells(i, j).Value
Cells(l, 24).Value = Cells(i, 1).Value
Cells(l, 25).Value = k
Cells(l, 26).Value = Cells(2, j).Value
l = l + 1
Next k
Next i
Next j
End Sub
This macro returns the overall result in column X to Z in my sample file.
Mar 28 2022 09:15 AM
SolutionSub 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.