Compare 2 columns between sheets

[mrsriexcel]

Hi,
I have two sheets named Sheet1,Sheet2.
If ColumnA and ColumnC in Sheet1 is exactly matching with ColumnA and ColumnC in Sheet2, copy the entire matching record (A1 to Z1) in sheet2 to sheet3.

Please help me with Excel formula/VBA code for this scenario.

Regards,
Sri

 

[hiker95]

mrsriexcel,

Can we see you actual raw data workbook/worksheets?

And, can you have Sheet3 manually completed by you with the results you are looking for?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

Dropbox

 

[mrsriexcel]

mrsriexcel,

Can we see you actual raw data workbook/worksheets?

And, can you have Sheet3 manually completed by you with the results you are looking for?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

Dropbox

Attached here is the worksheet for your reference.

I have two sheets named Sheet1,Sheet2.
If ColumnA and ColumnB in Sheet1 is exactly matching with ColumnA and ColumnB in Sheet2, copy the entire matching record (A1 to Z1) in sheet2 to sheet3.

Dropbox - Book2.xlsx

 

[mart37]

=INDEX(Sheet1!$A$1:$Z$30,ALS(SUMPRODUCT((Sheet1!$A$1:$A$30&"-"&Sheet1!$B$1:$B$30=Sheet2!$A1&"-"&Sheet2!$B1)*ROW($1:$30))=0;NA();SUMPRODUCT((Sheet1!$A$1:$A$30&"-"&Sheet1!$B$1:$B$30=Sheet2!$A1&"-"&Sheet2!$B1)*ROW($1:$30)));COLUMN())

The rows with error can you filter and delete.

 

[hiker95]

Attached here is the worksheet for your reference.

I have two sheets named Sheet1,Sheet2.
If ColumnA and ColumnB in Sheet1 is exactly matching with ColumnA and ColumnB in Sheet2, copy the entire matching record (A1 to Z1) in sheet2 to sheet3.

Dropbox - Book2.xlsx

mrsriexcel,

Thanks for the latest workbook.

And, can you have Sheet3 manually completed by you with the results you are looking for?

Can you supply another workbook containing the raw data in worksheets Sheet1, and, Sheet2, and, can you have Sheet3 manually completed by you with the results you are looking for?

 

[Fluff]

Hia
Does this work for you

Sub CheckMove()

    Dim Dict As Object
    Dim Rng As Range
    Dim UsdRws1 As Long
    Dim UsdRws2 As Long
    Dim Chk As String

    UsdRws1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    UsdRws2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    Set Dict = CreateObject("scripting.dictionary")
    
    For Each Rng In Sheet2.Range("A2:A" & UsdRws2)
        Dict(Rng.Text & "|" & Rng.Offset(, 2).Text) = Rng.Row
    Next Rng

    For Each Rng In Sheet1.Range("A2:A" & UsdRws1)
    Chk = (Rng.Text & "|" & Rng.Offset(, 2).Text)
        If Dict.exists(Chk) Then
            Sheet2.Rows(Dict.Item(Chk)).Copy _
                Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next Rng
End Sub

 

[mrsriexcel]

Hia
Does this work for you

Sub CheckMove()

    Dim Dict As Object
    Dim Rng As Range
    Dim UsdRws1 As Long
    Dim UsdRws2 As Long
    Dim Chk As String

    UsdRws1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    UsdRws2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    Set Dict = CreateObject("scripting.dictionary")
    
    For Each Rng In Sheet2.Range("A2:A" & UsdRws2)
        Dict(Rng.Text & "|" & Rng.Offset(, 2).Text) = Rng.Row
    Next Rng

    For Each Rng In Sheet1.Range("A2:A" & UsdRws1)
    Chk = (Rng.Text & "|" & Rng.Offset(, 2).Text)
        If Dict.exists(Chk) Then
            Sheet2.Rows(Dict.Item(Chk)).Copy _
                Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next Rng
End Sub

This code is doing some copy from sheet 2, but code is not working as I expected...
Take the combination of Column A and B in Sheet 1 and check/search in Sheet2. The outcome of this search/records/Columns should copied over to Sheet3.

 

[mrsriexcel]

mrsriexcel,

Thanks for the latest workbook.




Can you supply another workbook containing the raw data in worksheets Sheet1, and, Sheet2, and, can you have Sheet3 manually completed by you with the results you are looking for?

**********

Updated my workbook with Sheet1, Sheet2 and Result in Sheet3.

Here is the link:

Dropbox - Book2.xlsx

 

[hiker95]

mrsriexcel,

Per you latest workbook/worksheets:

Does you actual raw data worksheets have titles in row 1, like the following?

Column1 Column2 Column3

 

[mrsriexcel]

mrsriexcel,

Per you latest workbook/worksheets:

Does you actual raw data worksheets have titles in row 1, like the following?

Column1 Column2 Column3

Yes, it has headers/Titles in row1.