Duplicating Rows based on Value from another table

[michaeldee]

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:

[#2]

Could you upload a sample Workbook ? I don't quite understand from the images....

[#3]

@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 

[#4]

@michaeldee 

[#5]

@michaeldee 

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. 

[#6]

@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.

[#7]

Thank you @OliverScheurich!

[#8]

Thank you @Riny_van_Eekelen. I was not very familiar with Power Query before today. However, I think I now have a solution as a result. Thank you very much