Must match two criterias

[excelnewbie]

Hello,

I have two spreadsheets with a column for Last Name and a column for
First Name. I need to compare both spreadsheets for an exact match of
Last Name and First Name. I have the following code:

Set CompareRange = Workbooks("Sheet1.xls"). _
Worksheets("Sheet2").Range("C4:C3800,D43800")
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 2) = x
Next y
Next x

This code is working fine, but it doesn't bring me the matches of both
Last Name and First Name together. Ex.

If I want to know if Smith, Joe is on sheet 2.....
In Sheet 1 I have Smith, Joe and in Sheet 2 I have Saunders, Joe and
Smith, Erik it will come up that it did find Smith, Joe because it is
doing it individually. I need it to find the exact match of First namd
AND Last name. First and Last name must be the same. Any
suggestions??

Thank you!

 

[Allen]

Can't you concatenate the names together in the
spreadsheet itself? Then things will get much easier.

Allen

 

[TEAMRACING]

Try a concatenate function. Basically, combines the two
strings into one.

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])"