Compare 2 columns from sheet 1 with 2 columns in sheet 2 in single workbook

[Gogo007]

I have single workbook having 2 sheets(sheet1,sheet2).
I wanted to compare 2 columns(A,B) value from sheet1 with 2 columns(C,D), if they match then in in next column of sheet 1 cell value should be "Match" and if not then "Not match")
In sheet1(A column should equal to ==)sheet2(C column)
In sheet1(B column should equal to ==)sheet2(D column)


e.g 1
SHEET1 SHEET2
A B C D E
101 2000 101 2000 Match (All 4 values are same)

e.g 2
SHEET1 SHEET2
A B C D E
101 2000 201 2000 Not Match (A and C not equal)


e.g 3
SHEET1 SHEET2
A B C D E
101 2000 101 3000 Not Match (B and D are not equal)

e.g 4
SHEET1 SHEET2
A B C D E
301 7000 101 3000 Not Match (A and B not equal as well as B and D are not equal)


please if anyone can help me in this

 

[Gogo007]

How about

Book1
	A	B	C
1
2	101	1000	Match row 2
3	301	3000	Match row 4
4	401	3000	No Match
5	301	5000	No Match
6	501	5000	Match row 6
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=LET(Rws,FILTER(ROW(Sheet2!$C$2:$D$10),(Sheet2!$C$2:$C$10=A2)*(Sheet2!$D$2:$D$10=B2)), IF(COUNT(Rws),"Match row "&Rws,"No Match"))

It's giving an error , "This function isn't valid ", Also I want to do this in VBA. Match Column along with row number in Sheet1 and on sheet2 as well.
Would be great help from till now .
Thank You so much.

 

[maabadi]

This is VBA Mathod:

Sub test()
    Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
    Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
     For i = 2 To Lr1
     For j = 2 To Lr2
      If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
       If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
       Sheets("sheet1").Range("D" & i).Value = "Match"
       Sheets("sheet1").Range("E" & i).Value = j
       GoTo Step2
       Else
       Sheets("sheet1").Range("D" & i).Value = "Not Match"
       End If
      Else
      Sheets("sheet1").Range("D" & i).Value = "Not Match"
      End If
     Next j
Step2:
     Next i
End Sub

 

[Gogo007]

This is VBA Mathod:

Sub test()
    Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
    Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
     For i = 2 To Lr1
     For j = 2 To Lr2
      If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
       If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
       Sheets("sheet1").Range("D" & i).Value = "Match"
       Sheets("sheet1").Range("E" & i).Value = j
       GoTo Step2
       Else
       Sheets("sheet1").Range("D" & i).Value = "Not Match"
       End If
      Else
      Sheets("sheet1").Range("D" & i).Value = "Not Match"
      End If
     Next j
Step2:
     Next i
End Sub

Thank You so much buddy , just 1 more help can we add Row number where its "Match".

 

[maabadi]

I added it to See at Column E at Sheet 1. Are you don't see it?

 

[Gogo007]

I added it to See at Column E at Sheet 1. Are you don't see it?

Yes, I can see it.
This is the perfect code with Row Number, Thank You so much
Sorry I missed it by mistake.

One more help in this Code
After running this Code,
If the Case is "Unmatched" then I want to Check again if, Column B from sheet1 with Column D from Sheet2 is matching or not and if matched then should Print Match with Row Number, like previous code.

 

[maabadi]

Then how you determine between 2 column match with 4 column match at Result?
If only Column B match with column D, I think it's better to use another criteria.
For e.g. 4 column Match = "Complete Match"
2 Column Match = "Partial Match"
Are you want this?
.

 

[Gogo007]

Then how you determine between 2 column match with 4 column match at Result?
If only Column B match with column D, I think it's better to use another criteria.
For e.g. 4 column Match = "Complete Match"
2 Column Match = "Partial Match"
Are you want this?
.

I totally appreciate your logic.
Yes We can go with this.
If 4 column match then "Complete Match" with row number
if 2 column match{Column B from sheet1 with Column D from Sheet2 } then "Partial Match " with row number.

 

[maabadi]

Try this:

Sub test()
    Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
    Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
     For i = 2 To Lr1
     For j = 2 To Lr2
      If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
        If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
          Sheets("sheet1").Range("D" & i).Value = "Complete Match"
          Sheets("sheet1").Range("E" & i).Value = j
       GoTo Step2
       Else
         Sheets("sheet1").Range("D" & i).Value = "Partial Match"
         Sheets("sheet1").Range("E" & i).Value = j
       End If
      Else
      Sheets("sheet1").Range("D" & i).Value = "Not Match"
      End If
     Next j
Step2:
     Next i
End Sub

 

[Gogo007]

Try this:

Sub test()
    Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long
    Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
     For i = 2 To Lr1
     For j = 2 To Lr2
      If Sheets("sheet1").Range("B" & i).Value = Sheets("sheet2").Range("D" & j).Value Then
        If Sheets("sheet1").Range("A" & i).Value = Sheets("sheet2").Range("C" & j).Value Then
          Sheets("sheet1").Range("D" & i).Value = "Complete Match"
          Sheets("sheet1").Range("E" & i).Value = j
       GoTo Step2
       Else
         Sheets("sheet1").Range("D" & i).Value = "Partial Match"
         Sheets("sheet1").Range("E" & i).Value = j
       End If
      Else
      Sheets("sheet1").Range("D" & i).Value = "Not Match"
      End If
     Next j
Step2:
     Next i
End Sub

Hello,
Thanks for your help,
I am finding the below Results

As It's showing the Row Number when Amount is same but Not showing the Title as "Partial Match" .
Otherwise everything is absolutely on Mark.
Please help in this , and correct me If I am missing something.

Thank You.

 

[Gogo007]

Hello,
Thanks for your help,
I am finding the below Results

View attachment 37854

View attachment 37855
As It's showing the Row Number when Amount is same but Not showing the Title as "Partial Match" .
Otherwise everything is absolutely on Mark.
Please help in this , and correct me If I am missing something.

Thank You.

Basically , Everything is working fine but only "Partial Match" is not printing.