Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARE CELL CONTENTS
Please click the link below:
http://cjoint.com/data/mxvW30Cruc.htm The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). First, why the result shows #VALUE! What's wrong with the formula? The goal is to check the combination of [first name, middle name and last name] against the signature. But sometimes the signature can be considered as correct (TRUE, as in E3 & E4) even if it is wrong. What's tricky is we don't need the signature to be exactly the same as the combination of the 3 names, so I can't use EXACT function. For example, what's calculated as FALSE (E5) will be filtered and manually checked to see if the spelling mistakes are acceptable or not.So I changed the above formula to IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(B2&C2&D2)))2,FALSE,TRUE),FALSE) As a result, all records with FALSE or #VALUE! need further manual checking. 1. As the number of records is very large (10000 records), the manual checking precedure takes a extremely long time. Can anyone suggest better ways to do the task? Thanks! 2. There are a number of keywords forbidden to use in the signature, say the list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL? Thanks a lot!!!!! Thanks!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARE CELL CONTENTS
This will take care of the value errors
=AND(ISNUMBER(SEARCH(B3,A3)),ISNUMBER(SEARCH(C3,A3 )),ISNUMBER(SEARCH(D3,A3)) ,(LEN(A3)-SUM(LEN(B3&C3&D3)))2) however since I don't know what the premises are for what is correct and incorrect I don't know about the rest, I assume you could test for empty cells etc -- Regards, Peo Sjoblom "guy" wrote in message ... Please click the link below: http://cjoint.com/data/mxvW30Cruc.htm The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). First, why the result shows #VALUE! What's wrong with the formula? The goal is to check the combination of [first name, middle name and last name] against the signature. But sometimes the signature can be considered as correct (TRUE, as in E3 & E4) even if it is wrong. What's tricky is we don't need the signature to be exactly the same as the combination of the 3 names, so I can't use EXACT function. For example, what's calculated as FALSE (E5) will be filtered and manually checked to see if the spelling mistakes are acceptable or not.So I changed the above formula to IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN( B2&C2&D2)))2,FALSE,TRUE),FALSE) As a result, all records with FALSE or #VALUE! need further manual checking. 1. As the number of records is very large (10000 records), the manual checking precedure takes a extremely long time. Can anyone suggest better ways to do the task? Thanks! 2. There are a number of keywords forbidden to use in the signature, say the list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL? Thanks a lot!!!!! Thanks!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARE CELL CONTENTS
Guy,
First, there is nothing wrong with the formula. SEARCH returns #VALUE! if the text is not found. Try consulting the online help for this function for further clarification. If you want to suppress the error then you can use an IF...THEN to test if an error is returned to set the error to a value of your choice like FALSE. I can't really comment on the latter portion of your post because I struggle to understand what you are trying to accomplish. M C Del Papa "guy" wrote in message ... Please click the link below: http://cjoint.com/data/mxvW30Cruc.htm The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). First, why the result shows #VALUE! What's wrong with the formula? The goal is to check the combination of [first name, middle name and last name] against the signature. But sometimes the signature can be considered as correct (TRUE, as in E3 & E4) even if it is wrong. What's tricky is we don't need the signature to be exactly the same as the combination of the 3 names, so I can't use EXACT function. For example, what's calculated as FALSE (E5) will be filtered and manually checked to see if the spelling mistakes are acceptable or not.So I changed the above formula to IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(B2&C2&D2)))2,FALSE,TRUE),FALSE) As a result, all records with FALSE or #VALUE! need further manual checking. 1. As the number of records is very large (10000 records), the manual checking precedure takes a extremely long time. Can anyone suggest better ways to do the task? Thanks! 2. There are a number of keywords forbidden to use in the signature, say the list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL? Thanks a lot!!!!! Thanks!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARE CELL CONTENTS
thanks!
"M C Del Papa" s.prodigy.net... Guy, First, there is nothing wrong with the formula. SEARCH returns #VALUE! if the text is not found. Try consulting the online help for this function for further clarification. If you want to suppress the error then you can use an IF...THEN to test if an error is returned to set the error to a value of your choice like FALSE. I can't really comment on the latter portion of your post because I struggle to understand what you are trying to accomplish. M C Del Papa "guy" wrote in message ... Please click the link below: http://cjoint.com/data/mxvW30Cruc.htm The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). First, why the result shows #VALUE! What's wrong with the formula? The goal is to check the combination of [first name, middle name and last name] against the signature. But sometimes the signature can be considered as correct (TRUE, as in E3 & E4) even if it is wrong. What's tricky is we don't need the signature to be exactly the same as the combination of the 3 names, so I can't use EXACT function. For example, what's calculated as FALSE (E5) will be filtered and manually checked to see if the spelling mistakes are acceptable or not.So I changed the above formula to IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(B2&C2&D2)))2,FALSE,TRUE),FALSE) As a result, all records with FALSE or #VALUE! need further manual checking. 1. As the number of records is very large (10000 records), the manual checking precedure takes a extremely long time. Can anyone suggest better ways to do the task? Thanks! 2. There are a number of keywords forbidden to use in the signature, say the list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL? Thanks a lot!!!!! Thanks!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPARE CELL CONTENTS
thanks!
"Peo Sjoblom" bl... This will take care of the value errors =AND(ISNUMBER(SEARCH(B3,A3)),ISNUMBER(SEARCH(C3,A3 )),ISNUMBER(SEARCH(D3,A3)) ,(LEN(A3)-SUM(LEN(B3&C3&D3)))2) however since I don't know what the premises are for what is correct and incorrect I don't know about the rest, I assume you could test for empty cells etc -- Regards, Peo Sjoblom "guy" wrote in message ... Please click the link below: http://cjoint.com/data/mxvW30Cruc.htm The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). First, why the result shows #VALUE! What's wrong with the formula? The goal is to check the combination of [first name, middle name and last name] against the signature. But sometimes the signature can be considered as correct (TRUE, as in E3 & E4) even if it is wrong. What's tricky is we don't need the signature to be exactly the same as the combination of the 3 names, so I can't use EXACT function. For example, what's calculated as FALSE (E5) will be filtered and manually checked to see if the spelling mistakes are acceptable or not.So I changed the above formula to IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN( B2&C2&D2)))2,FALSE,TRUE),FALSE) As a result, all records with FALSE or #VALUE! need further manual checking. 1. As the number of records is very large (10000 records), the manual checking precedure takes a extremely long time. Can anyone suggest better ways to do the task? Thanks! 2. There are a number of keywords forbidden to use in the signature, say the list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL? Thanks a lot!!!!! Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell contents as the 'lookup value' parameter in HLOOKUP function | Excel Discussion (Misc queries) | |||
Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
cell color index comparison | New Users to Excel | |||
cell contents | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |