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

#### Gogo007

##### New Member
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

##### Well-known Member
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Gogo007

##### New Member
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.

#### Attachments

##### Well-known Member
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``````

Replies
7
Views
445
Replies
2
Views
71
Replies
0
Views
82
Replies
3
Views
72
Replies
3
Views
34

### Forum statistics

1,143,835
Messages
5,721,054
Members
422,338
Latest member
hat360 ### 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.

### Which adblocker are you using?    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

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