How to compare two columns from different worksheet

[Ameya]

Hi friends,

I need to compare two columns from different worksheets but they are in same workbook.I want that all data from worksheet 2 of column one matches any and all data from worksheet 1 of column one , then it should go back to worksheet 2-column A on the same data matching row, copy the data from that cell and paste it into the same row of the data match in worksheet 1-column A and paste it.

I know it very difficult to do but i am sure we can do that.

Can anyone help me out with this issue.

[Dr.pter]

Enter this array formula in cell [Book1]Sheet1!C2:

=INDEX([Book2.xls]Sheet1!$C$2:$C$1000,MIN(IF(A2&"|"&CHOOSE(RIGHT(B2,1),"first visit","second visit","third visit")=A2&"|"&[Book2.xls]Sheet1!$B$2:$B$1000,ROW([Book2.xls]Sheet1!$B$2:$B$1000)-1,"")))

Enter by pressing CTRL+SHIFT+ENTER. Copy down as far as needed.

[Ameya]

Thanks for replying me but the above code is not working.

[Solomon]

Try to use the following code i am sure it will work for you.

Sub CompareSheets()
Dim Sh1LastRow As Variant
Dim Sh1Range As Range
Dim Sh1Cell As Range
Dim Sh2LastRow As Variant
Dim Sh2Range As Range
Dim c As Variant

With Sheets(1)
   Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
   Set Sh1Range = .Range("A1:A" & Sh1LastRow)
End With
With Sheets(2)
   Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
   Set Sh2Range = .Range("A1:A" & Sh2LastRow)
End With

'compare sheet 1 with sheet 2
For Each Sh1Cell In Sh1Range
   Set c = Sh2Range.Find( _
      what:=Sh1Cell, LookIn:=xlValues)
   If c Is Nothing Then
      Sh1Cell.Interior.ColorIndex = 3
      Sh1Cell.Offset(0, 1).Interior.ColorIndex = 3 'Red
   Else
      If Sh1Cell.Offset(0, 1) <> c.Offset(0, 1) Then
         Sh1Cell.Offset(0, 1).Interior.ColorIndex = 4 'Green
      End If
   End If
Next Sh1Cell

'compare sheet 2 with sheet 1
For Each Sh2Cell In Sh2Range
   Set c = Sh1Range.Find( _
      what:=Sh2Cell, LookIn:=xlValues)
   If c Is Nothing Then
      Sh2Cell.Interior.ColorIndex = 3
      Sh2Cell.Offset(0, 1).Interior.ColorIndex = 3 'Red
   Else
      If Sh2Cell.Offset(0, 1) <> c.Offset(0, 1) Then
         Sh2Cell.Offset(0, 1).Interior.ColorIndex = 4 'Green
      End If
   End If
Next Sh2Cell

End Sub