how can compare data between two worksheets?

[biff]

What's the best way to compare data between two worksheets and highlight the
similarities?

Also say you are comparing a list of people's names but one worksheet has a
column that has the person's full name preceded by title while the other list
has separate columns for first and last name? How could you compare the
names in that situation?

 

[Billy Liddel]

The way you have your sheets set up makes it difficult to be sure. The
following macro will work but can give a wrong result (see below).

Sub t()
Dim rngToSearch As Range, rngToCheck As Range
Dim c, d, sName As String

Set rngToSearch = Sheets("Sheet1").Range("A2:A20") 'change to suit
Set rngToCheck = Sheets("Sheet2").Range("A2:A20") 'change to suit

For Each c In rngToCheck
sName = c & " " & c.Offset(0, 1) & " "
For Each d In rngToSearch
If IsNumeric(Application.Search(sName, d)) Then
If Len(c) > 0 Then
c.Offset(0, 2) = "Found"
Else
c.Offset(0, 2) = "Not Found"
End If
End If
Next d
Next
End Sub

The macro will display the result in column C and assumes that the list only
has two columns. If you want to enter the result in column F change the
offset to (0,6).

To use the macro press ALT + F11 to open the VB Editor, Press Insert, Module
and copy the code into the new module. Close the Editor and in the worksheet
press ALT + F8, select the macro and click Run.

The problem occurs if the First Name column contains a name that is in the
Full name list and the Last name is left empty.

Say the full name list includes Mr Martin Smith and the FName contains
Martin and the LName is blank - in this case it is returned as 'Found',
However, if a First Name is Martin and the Last name is Blundel then, unless
he is somewhere in the Full Name List, he is declared 'Not Found'

HTH
Peter

 

[Billy Liddel]

The following revided code may be better

Sub t()
Dim rngToSearch As Range, rngToCheck As Range
Dim c, d, sName1 As String, sName2 As String

Set rngToSearch = Sheets("Sheet1").Range("A2:A20") 'change to suit
Set rngToCheck = Sheets("Sheet2").Range("A2:A20") 'change to suit

For Each c In rngToCheck

sName1 = c
sName2 = c.Offset(0, 1)
For Each d In rngToSearch
If Len(sName1 & sName2) = 0 Then Exit For
If IsNumeric(Application.Search(sName1, d)) Then
If IsNumeric(Application.Search(sName2, d)) Then
c.Offset(0, 2) = "Found"
Exit For
End If
Else
c.Offset(0, 2) = "Not Found"
End If
Next d
Next
End Sub

Peter