Forum Discussion
Duplicating Rows based on Value from another table
Hi,
I have two tables of data. Both will be held on two separate sheets in a workbook. Where there is a duplicate value in Table 2, I need to duplicate the row of data which contains this value in table 1. Both duplicate values need to be recorded on table one.
Can this be done via Formulae or Conditional Formatting? Or would VBA be the best option?
Before:
After:
7 Replies
Sub duplicates() Dim i As Integer Dim j As Integer Dim w As Integer Dim z As Integer Range("I:J").Clear z = 3 For i = 3 To 20 j = Application.WorksheetFunction.CountIf(Range(Cells(3, 5), Cells(14, 5)), Cells(i, 2)) If j = 1 Then Range(Cells(i, 2), Cells(i, 3)).Copy Cells(z, 9) z = z + 1 Else If j = 2 Then w = Application.WorksheetFunction.Match(Cells(i, 2), Range(Cells(3, 5), Cells(14, 5)), 0) + 2 If Cells(i, 3).Value = Cells(w, 6).Value Then Range(Cells(w, 5), Cells(w + 1, 6)).Copy Range(Cells(z, 9), Cells(z + 1, 10)) z = z + 2 Else Range(Cells(i, 2), Cells(i, 3)).Copy Cells(z, 9) z = z + 1 End If Else Range(Cells(i, 2), Cells(i, 3)).Copy Cells(z, 9) z = z + 1 End If End If Next i End SubMaybe with these lines of code. You can click the button in cell M2 in the attached file to start the macro.
Table2 has to be sorted by the Level Codes and there can be duplicate Level Codes but no triplicates, quadruplicates and so on. If i understand your question correctly there must be an exact match for Level Code and Level ID.
- Thank you OliverScheurich!
- Could you upload a sample Workbook ? I don't quite understand from the images....
Cambosity100 Sure. See attached.
On the sheet called "Data to Load" - This is a sheet which will be used to prepare data for loading into SQL.
On the sheet called "Reference Data", this is data which will be used for referencing, in otherwords, we will perform a vlookups to this sheet to return the level ID associated with the Level Code.
In the example on the attached, there are two level codes with the value "SO" in the column called Level Code on the sheet called reference data. Both have two unique IDs and therefore, both are valid.
On the tab called "Data to Load", there is one record which contains the Level Code of SO in the column called Level Code. However, as there are actually two distinct records for SO (as shown on the reference data sheet), we would need to duplicate the line in yellow, one showing level ID 6 and the other showing level id 834.
I hope this explains
