Must match two criterias

  • Thread starter Thread starter excelnewbie
  • Start date Start date
E

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,D4:D3800")
' 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!
 
Can't you concatenate the names together in the
spreadsheet itself? Then things will get much easier.

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

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