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

Gogo007

New Member
Joined
Feb 22, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,822
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Sheet1:
Cell Formulas
RangeFormula
E2:E6E2=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:F6F2=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match","Not Match")


Sheet2:
Book1.xlsx
ABCDE
1
21011000
32012000
43013000
54014000
65015000
7
Sheet2
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
VBA?
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,822
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi @mohadin . I think this Line
VBA Code:
a = Sheets("sheet2").Range("d2").CurrentRegion
Should be:
VBA Code:
a = Sheets("sheet2").Range("c2").CurrentRegion
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@maabadi
Yes in deed.
Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff 1.xlsm
ABCD
1
21011000
32012000
43013000
54014000
65015000
7
8
Sheet2


+Fluff 1.xlsm
ABC
1
21011000Match
33013000Match
44013000No Match
53015000No Match
65015000Match
7
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
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

New Member
Joined
Feb 22, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
T
Try this:
Sheet1:
Cell Formulas
RangeFormula
E2:E6E2=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:F6F2=IF((Sheet1!A2=Sheet2!C2)*(Sheet1!B2=Sheet2!D2),"Match","Not Match")


Sheet2:
Book1.xlsx
ABCDE
1
21011000
32012000
43013000
54014000
65015000
7
Sheet2
Thanks a lot buddy ,
Can we find out the row no when its a MATCH
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
How about
Book1
ABC
1
21011000Match row 2
33013000Match row 4
44013000No Match
53015000No Match
65015000Match row 6
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=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

New Member
Joined
Feb 22, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,902
Messages
5,638,896
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top