May 06 2022 11:48 AM
May 06 2022 11:48 AM
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?
May 06 2022 03:52 PM - edited May 06 2022 03:53 PM
@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
May 06 2022 04:27 PM
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 Sub
Maybe 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.
May 06 2022 11:48 PM
@michaeldee As a variant, PowerQuery can do this swiftly without having to bother about VLOOKUP. Attached file contains a working example based on the data set you provided. PQ connects to the blue tables and spits out the green one (in the Upload sheet) upon a refresh. No VBA needed and much easier to maintain and adapt.
May 07 2022 10:33 AM