Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Must match two criterias
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! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Must match two criterias
Can't you concatenate the names together in the
spreadsheet itself? Then things will get much easier. Allen -----Original Message----- 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! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Must match two criterias
Try a concatenate function. Basically, combines the two
strings into one. ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])" -----Original Message----- 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! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match two criterias and give a third colums result | Excel Worksheet Functions | |||
VLOOKUP MATCH INDEX two conditions / criterias text and date | Excel Worksheet Functions | |||
Too many criterias... | Excel Worksheet Functions | |||
If Criterias | Excel Discussion (Misc queries) | |||
Sum If using 2 criterias | Excel Discussion (Misc queries) |