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

 

[maabadi]

Try this:
Sheet1:

Book1.xlsx
	A	B	C	D	E	F	G
1					Method 1	Method 2
2	101	1000			Match(All 4 Values Same)	Match
3	201	3000			Not Match(B & D Not Equal)	Not Match
4	401	3000			Not Match(A & C Not Equal)	Not Match
5	301	5000			Not Match(A and B not equal as well as B and D are not equal)	Not Match
6	501	5000			Match(All 4 Values Same)	Match
7
8
9
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match(All 4 Values Same)",IF((Sheet1!A2<>Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Not Match(A & C Not Equal)",IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2<>Sheet2!D2),"Not Match(B & D Not Equal)","Not Match(A and B not equal as well as B and D are not equal)")))
F2:F6	F2	=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match","Not Match")

Sheet2:

Book1.xlsx
	A	B	C	D	E
1
2			101	1000
3			201	2000
4			301	3000
5			401	4000
6			501	5000
7
Sheet2

 

[mohadin]

Hi
VBA?

Sub test()
    Dim a As Variant, i
    a = Sheets("sheet1").Range("a2").CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1) & a(i, 2)) Then
                    .Add (a(i, 1) & a(i, 2)), ""
                End If
            End If
        Next
        a = Sheets("sheet2").Range("d2").CurrentRegion
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1) & a(i, 2)) Then
                a(i, 1) = "Not Match"
            Else
                 a(i, 1) = "Match"
            End If
        Next
       Sheets("Sheet1").Cells(2, 3).Resize(UBound(a)) = a
       End With
End Sub

 

[maabadi]

Hi @mohadin . I think this Line

a = Sheets("sheet2").Range("d2").CurrentRegion

Should be:

a = Sheets("sheet2").Range("c2").CurrentRegion

 

[mohadin]

@maabadi
Yes in deed.
Thank you

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D
1
2			101	1000
3			201	2000
4			301	3000
5			401	4000
6			501	5000
7
8
Sheet2

+Fluff 1.xlsm
	A	B	C
1
2	101	1000	Match
3	301	3000	Match
4	401	3000	No Match
5	301	5000	No Match
6	501	5000	Match
7
Sheet1

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

 

[mohadin]

Sub test()
    Dim a As Variant, i
    a = Sheets("sheet1").Range("A2").CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1) & a(i, 2)) Then
                    .Add (a(i, 1) & a(i, 2)), ""
                End If
            End If
        Next
        a = Sheets("sheet2").Range("C2").CurrentRegion
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1) & a(i, 2)) Then
                a(i, 1) = "Not Match"
            Else
                 a(i, 1) = "Match"
            End If
        Next
       Sheets("Sheet1").Cells(2, 3).Resize(UBound(a)) = a
       End With
End Sub

 

[Gogo007]

1000

T

Try this:
Sheet1:

Book1.xlsx
	A	B	C	D	E	F	G
1					Method 1	Method 2
2	101	1000			Match(All 4 Values Same)	Match
3	201	3000			Not Match(B & D Not Equal)	Not Match
4	401	3000			Not Match(A & C Not Equal)	Not Match
5	301	5000			Not Match(A and B not equal as well as B and D are not equal)	Not Match
6	501	5000			Match(All 4 Values Same)	Match
7
8
9
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match(All 4 Values Same)",IF((Sheet1!A2<>Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Not Match(A & C Not Equal)",IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2<>Sheet2!D2),"Not Match(B & D Not Equal)","Not Match(A and B not equal as well as B and D are not equal)")))
F2:F6	F2	=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match","Not Match")

Sheet2:

Book1.xlsx
	A	B	C	D	E
1
2			101	1000
3			201	2000
4			301	3000
5			401	4000
6			501	5000
7
Sheet2

Thanks a lot buddy ,
Can we find out the row no when its a MATCH

 

[Fluff]

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"))

 

[Gogo007]

Hello I am trying below code and giving me error on : "Match" i.e Expected end of statement
Worksheets("MOVEX").Range("X2").Formula = "=IF((MOVEX!E2=Bank!J2)*(MOVEX!P2=Bank!G2), "Match","Not Match")"
Lrs = Worksheets("MOVEX").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("MOVEX").Range("X2:X" & Lrs).FillDown