Excel - Match similar names
Asked By Sam
26-Jan-10 06:18 AM
A1 B1
Frank Mark Allen Frank Mark Allen Frank Mark
I want to compare two cells. A1 contain full name , B1 contain short name .
searching for formula to do a minimum match.
SUBSTITUTE
(1)
ISNUMBER
(1)
FIND
(1)
TRIM
(1)
LEFT
(1)
REPT
(1)
MID
(1)
Jacob Skaria replied to Sam
This might work for you...Try the below formula in cell C1...
=IF(ISNUMBER(SEARCH(B1,A1)),"Probable Match","No match")
--
Jacob
Ron Rosenfeld replied to Sam
I made the following assumptions:
The short name will have two and only two words
If the two names in the short name exist in the same order in the long name
(but not necessarily adjacent), then there is a match.
=IF(ISNUMBER(FIND(LEFT(B1,FIND(" ",B1)-1),A1)+
FIND(TRIM(MID(B1,FIND(" ",B1),99)),A1,FIND(
LEFT(B1,FIND(" ",B1)-1),A1))),"Match","No Match")
--ron
Jacob Skaria replied to Sam
Try the below in cell C1 which will identify the last name from cell B1 and
search in A1 for a match..You can add more conditions depending on your
data...For example check whether the 1st character of both cells are same..
=IF(ISNUMBER(SEARCH(TRIM(RIGHT(SUBSTITUTE(B1," ",
REPT(" ",255)),255)),A1)),"Match","No match")
--
Jacob
Columns O and R are the ???totals??? columns Excel Miscellaneous Discussions ISERR (1) SUMIF (1) FIND (1) LEFT (1) Fred Smith (1) Do you have a question you want answered? Regards, Fred Sorry given to me through the discussion group: = SUMIF(B11:O11, "A", B12:O12)+IF(ISERR(FIND("A, ", B12)> 0), 0, VALUE(LEFT(A12, FIND(", ", A12)-1)))+ IF(ISERR(FIND("A, ", B11)> 0), 0, VALUE(LEFT(B12, FIND(", ", B12)-1)))+ IF(ISERR(FIND("A, ", C11)> 0), 0, VALUE(LEFT(C12, FIND(", ", C12)-1
TIA and thanks for any ideas. Jim Excel Worksheet Discussions Microsoft Excel (1) Excel (1) SUBSTITUTE (1) DATEVALUE (1) Worksheet (1) Relative (1) Workbook (1) COUNT (1) I am going to which we assume are the abbreviation for a month, as "Jan", "Feb", etc. in A2: = LEFT(RIGHT(A1, LEN(A1)-FIND("]", A1)), 3) next we find out which month number the month is in A3: = MATCH(LEFT(RIGHT(A1, LEN(A1)-FIND("]", A1)), 3), {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 0 given worksheet to get the date from the sheet's name: = DATE(YEAR(NOW()), MATCH(LEFT(RIGHT(CELL("filename", A1), LEN(CELL("filename", A1))-FIND("]", CELL("filename", A1))), 3), {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov
K3).Select End With End If End If End If 'J6 has CN:CN, or: = SUBSTITUTE(SUBSTITUTE(CELL("address", $CN6), "$", ""), ROW(), "")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address", $CN6), "$", ""), ROW(), "") 'K3 has CW:CW, or: = SUBSTITUTE(SUBSTITUTE(CELL("address", $CW3), "$", ""), ROW(), "")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address", $CW3), "$", ""), ROW(), "") 'M4 has D:D, or: = SUBSTITUTE(SUBSTITUTE(CELL("address", $D4), "$", ""), ROW
formatted #'s converted to a usable / divisable #. Thanks, Steve Excel Worksheet Discussions Microsoft Excel (1) SUBSTITUTE (1) Worksheet (1) ISNUMBER (1) Macro (1) TEXT (1) REPT (1) CHAR (1) One way: = - -TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 10)), 10)) One way. . . Assuming the word "Days:" is *always* present. = - -MID(A1 spaces in your data, which could cause the #Value! error. Try this instead: B1 = - -RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", 3)), 3) This produced :6 I changed the 3's to 2's are just subtracting the first date from the second date to get "Days": = MID(A1, FIND(" to ", A1)+4, 11)-MID(A1, FIND(" to ", A1)-10, 11) As for your other problem, put this in a blank cell
the surname column. Thanks. Jeff Excel Discussions Microsoft Excel (1) Excel 2007 (1) Word (1) SUBSTITUTE (1) ISNUMBER (1) LEFT (1) FIND (1) REPT (1) See Chip's site for extracting first, middle and last names. http htm Gord Dibben MS Excel MVP Hello, You could try these formlulas: First word: = IF(ISNUMBER(FIND(" ", A1)), LEFT(A1, FIND(" ", A1)-1), A1) Last Word: = IF(ISNUMBER(FIND(" ", A1)), MID(SUBSTITUTE(A1, " If A1 may