Compating all rows in two sheets

[Utkarsh]

I have two sheets with similar data

Sheet1
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 25
a 01-03-2008 x 124

Sheet2
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 20
a 01-03-2008 x 124
d 01-05-2007 x 100

I am using the below code to flag enties that are not exactly
matching. Thus it should flag for me that Name"b" and "d" are not
found in Sheet1. It works propoerly for "d" but not for "b". Obviously
I am amking a mistake. Please advise.

Sub compare()
'clear columne
Sheets("Sheet2").Range("E:E").ClearContents

'Find the last row to be evaluated for each sheet
Sheets("Sheet1").Select
lastrow1 = Range("A65536").End(xlUp).Row
Sheets("Sheet2").Select
lastrow2 = Range("A65536").End(xlUp).Row

Sheets("Sheet1").Select
For i = 2 To lastrow1
'Transfer each foled for a row into a variable
rec1 = Cells(i, 1).Value
rec2 = Cells(i, 2).Value
rec3 = Cells(i, 3).Value
rec4 = Cells(i, 4).Value


Sheets("Sheet2").Select
For j = 2 To lastrow2

'Look for a match
If Cells(i, 1).Value = rec1 And Cells(i, 2).Value = rec2 And _
Cells(i, 3).Value = rec3 And _
Cells(i, 4).Value = rec4 Then

Cells(i, 5).Value = "Found"

End If

Next j
Next i

End Sub

 

[Jim Cone]

The first thing that sticks out is the selection of Sheet1 is done outside of both loops.
So most of your code it is comparing sheet2 to sheet2.
You could place the selection inside the loop and see what happens.
I have not tried to run your code...
'---
Sheets("Sheet1").Select
For i = 2 To lastrow1

-should be-

For i = 2 To lastrow1
Sheets("Sheet1").Select
---
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion
(compare stuff)
..
..
..

"Utkarsh" <[email protected]>
wrote in message
I have two sheets with similar data

Sheet1
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 25
a 01-03-2008 x 124

Sheet2
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 20
a 01-03-2008 x 124
d 01-05-2007 x 100

I am using the below code to flag enties that are not exactly
matching. Thus it should flag for me that Name"b" and "d" are not
found in Sheet1. It works propoerly for "d" but not for "b". Obviously
I am amking a mistake. Please advise.

Sub compare()
'clear columne
Sheets("Sheet2").Range("E:E").ClearContents

'Find the last row to be evaluated for each sheet
Sheets("Sheet1").Select
lastrow1 = Range("A65536").End(xlUp).Row
Sheets("Sheet2").Select
lastrow2 = Range("A65536").End(xlUp).Row

Sheets("Sheet1").Select
For i = 2 To lastrow1
'Transfer each foled for a row into a variable
rec1 = Cells(i, 1).Value
rec2 = Cells(i, 2).Value
rec3 = Cells(i, 3).Value
rec4 = Cells(i, 4).Value


Sheets("Sheet2").Select
For j = 2 To lastrow2

'Look for a match
If Cells(i, 1).Value = rec1 And Cells(i, 2).Value = rec2 And _
Cells(i, 3).Value = rec3 And _
Cells(i, 4).Value = rec4 Then

Cells(i, 5).Value = "Found"
End If
Next j
Next i
End Sub

 

[Jim Cone]

You also need to replace the "i" values with "j" in this part of the second loop....
'--
If Cells(i, 1).Value = rec1 And Cells(i, 2).Value = rec2 And _
Cells(i, 3).Value = rec3 And _
Cells(i, 4).Value = rec4 Then

Cells(i, 5).Value = "Found"
End If
'--
Jim Cone
Portland, Oregon USA
Sub