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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry my fault. Try this:
VBA Code:
Sub test()
    Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long, K As Long, L As Long
    Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
    K = 0
     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"
          K = 2
          Sheets("sheet1").Range("E" & i).Value = j
       GoTo Step2
       Else
         Sheets("sheet1").Range("D" & i).Value = "Partial Match"
         K = 1
         Sheets("sheet1").Range("E" & i).Value = j
       End If
      ElseIf K < 1 Then
      Sheets("sheet1").Range("D" & i).Value = "Not Match"
      End If
     Next j
Step2:
      K = 0
     Next i
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Gogo007

New Member
Joined
Feb 22, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Sorry my fault. Try this:
VBA Code:
Sub test()
    Dim a As Variant, i As Long, j As Long, Lr1 As Long, Lr2 As Long, K As Long, L As Long
    Lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Lr2 = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
    K = 0
     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"
          K = 2
          Sheets("sheet1").Range("E" & i).Value = j
       GoTo Step2
       Else
         Sheets("sheet1").Range("D" & i).Value = "Partial Match"
         K = 1
         Sheets("sheet1").Range("E" & i).Value = j
       End If
      ElseIf K < 1 Then
      Sheets("sheet1").Range("D" & i).Value = "Not Match"
      End If
     Next j
Step2:
      K = 0
     Next i
End Sub
Thank You for the help,
I am finding some difficulties as below,
In sheet1 there are some values in Column B and those are present in Sheet2 Column D , it should give the result as "Partial Match" but it's not showing any result.
Can you please help me in this.
 

Attachments

  • sheet1.png
    sheet1.png
    12.6 KB · Views: 2
  • sheet2.png
    sheet2.png
    6.9 KB · Views: 2

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change Lr1 & Lr2 Lines (I think Line 3 & 4) to This:
VBA Code:
  Lr1 = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row
  Lr2 = Sheets("sheet2").Range("D" & Rows.Count).End(xlUp).Row
 

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