Search match value and insert data in rows

**christeen00** · 02-08-2012, 10:28 PM

Hi,
I've browsed related threads but I failed to find a relevant topic for my question.
I have two master data. Some records need to be duplicated.
Please refer to my attached file for details. My desired results is tabulated in sheet name "output".
Your help and guidance are appreciated.

**smuzoen** · 02-09-2012, 12:07 AM

You could do this with a macro. Add the code to a module and run the macro genOutput - see attached workbook

Option Explicit
Sub genOutput()
On Error GoTo errHandler
Dim k As Long, wsData1 As Worksheet, wsData2 As Worksheet, wsOutput As Worksheet
Dim compData, t As Long, branchLoc As String, fnd As Boolean
Set wsData1 = Worksheets("Data1")
Set wsData2 = Worksheets("Data2")
Set wsOutput = Worksheets("output")
wsOutput.Range("A2:E" & wsOutput.Cells(Rows.Count, "A").End(xlUp).Row).Clear
compData = wsData2.Range("A2:B" & wsData2.Cells(Rows.Count, "A").End(xlUp).Row)
For k = 2 To wsData1.Cells(Rows.Count, "A").End(xlUp).Row
wsData1.Range("A" & k).Resize(, 4).Copy
For t = LBound(compData) To UBound(compData)
If wsData1.Range("C" & k) = compData(t, 1) Then
branchLoc = compData(t, 2): fnd = True
wsOutput.Range("A" & wsOutput.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
wsOutput.Range("E" & wsOutput.Cells(Rows.Count, "E").End(xlUp).Row + 1).Value = compData(t, 2)
End If
Next
If fnd = False Then
wsOutput.Range("A" & wsOutput.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
wsOutput.Range("E" & wsOutput.Cells(Rows.Count, "E").End(xlUp).Row + 1).Value = "Not Found"
wsOutput.Range("E" & wsOutput.Cells(Rows.Count, "E").End(xlUp).Row).Interior.Color = 255
End If
fnd = False
Next
Exit Sub
errHandler:
MsgBox "Error generating output - Error " & Err.Number & _
" - " & Err.Description
End Sub