Forum Discussion
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 SubMaybe with these lines of VBA code. Click the button in cell G2 in the attached file to start the macro.
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!
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.
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:
- The PQ was on a separate worksheet from the beginning (named, e.g., VARIATIONS)
- A button click launches the PQ (or VBA)
- The PQ determines then copies the PRIMARY range data to the VARIATIONS worksheet (A2:H####)
- The PQ runs and creates the REFRESH table to the right of the copied PRIMARY range data (K2:R####)
- The REFRESH range data (K2:R####) is determined, copied and appended at the end of the PRIMARY
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.
