Combining repeated rows from 2 sheets' data based on a key

[pranavshandilya]

I am having 2 sheets in excel and I am trying to combine the data such that for every record in Sheet1, I get records from Sheet2 based on the common key. Below is the sample data to clarify:

I have SHEET 1 and SHEET 2 Data and I want to generate something like SHEET 3. Can you please suggest how can I do it.

 

[onlyadrafter]

Hello,

Does this work as expected?

Sub COPY_TO_SHEET_3()
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        For MY_ROWS = 2 To .Range("B" & Rows.Count).End(xlUp).Row
            MY_TEXT_A = .Range("A" & MY_ROWS).Value
            MY_TEXT_B = .Range("B" & MY_ROWS).Value
            Sheets("Sheet2").Select
            Range("A1:C" & Range("C" & Rows.Count).End(xlUp).Row).Select
            Selection.AutoFilter Field:=1, Criteria1:=MY_TEXT_B
            Range("A2:C" & Range("C" & Rows.Count).End(xlUp).Row).Select
            Selection.SpecialCells(xlCellTypeVisible).Copy
            With Sheets("Sheet3")
                .Range("C" & .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row).PasteSpecial (xlPasteAll)
                .Range("A" & .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = MY_TEXT_A
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = MY_TEXT_B
            .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row & ":B" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row & ":A" & .Range("C" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
            End With
            Sheets("Sheet2").Range("A1:C1").AutoFilter
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub