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

Gogo007

New Member
Joined
Feb 22, 2021
Messages
25
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
 

Gogo007

New Member
Joined
Feb 22, 2021
Messages
25
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"))
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.
 

Attachments

  • Annotation 2021-04-14 124720.png
    Annotation 2021-04-14 124720.png
    18.4 KB · Views: 4
  • sheet2.png
    sheet2.png
    9.4 KB · Views: 4

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is VBA Mathod:
VBA Code:
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

New Member
Joined
Feb 22, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
This is VBA Mathod:
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I added it to See at Column E at Sheet 1. Are you don't see it?
 

Gogo007

New Member
Joined
Feb 22, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 22, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
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

New Member
Joined
Feb 22, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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

sheet1.png


sheet2.png

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

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

Watch MrExcel Video

Forum statistics

Threads
1,130,252
Messages
5,641,113
Members
417,194
Latest member
Excellent Excel

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