| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare cell contents
A B C
D E 1 Signature First Name Middle Name Last Name Checking (T/F) 2 aaa bbb ccc aaa bbb ccc T 3 aaaa bbbb cccc dd aaa bbb ccc T (s/b wrong) 4 ddd eee fff ddd {Blank Cell} fff T (s/b wrong) 5 ddd eee fff ddd eee fffff F The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). 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(B2,C2,D2))=2,F,T),F) As a result, all records with FALSE 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
A B C
D E 1 Signature First Name Middle Name Last Name Checking (T/F) 2 aaa bbb ccc aaa bbb ccc T 3 aaaa bbbb cccc dd aaa bbb ccc T (s/b wrong) 4 ddd eee fff ddd {Blank Cell} fff T (s/b wrong) 5 ddd eee fff ddd eee fffff F The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). 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(B2,C2,D2))=2,F,T),F) As a result, all records with FALSE 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!!! "guy" bl... A B C D E 1 Signature First Name Middle Name Last Name Checking (T/F) 2 aaa bbb ccc aaa bbb ccc T 3 aaaa bbbb cccc dd aaa bbb ccc T (s/b wrong) 4 ddd eee fff ddd {Blank Cell} fff T (s/b wrong) 5 ddd eee fff ddd eee fffff F The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). 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(B2,C2,D2))=2,F,T),F) As a result, all records with FALSE 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
Please ignore the previous 2 posts... sorry.
"guy" .gbl... A B C D E 1 Signature First Name Middle Name Last Name Checking (T/F) 2 aaa bbb ccc aaa bbb ccc T 3 aaaa bbbb cccc dd aaa bbb ccc T (s/b wrong) 4 ddd eee fff ddd {Blank Cell} fff T (s/b wrong) 5 ddd eee fff ddd eee fffff F The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). 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(B2,C2,D2))=2,F,T),F) As a result, all records with FALSE 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!!! "guy" bl... A B C D E 1 Signature First Name Middle Name Last Name Checking (T/F) 2 aaa bbb ccc aaa bbb ccc T 3 aaaa bbbb cccc dd aaa bbb ccc T (s/b wrong) 4 ddd eee fff ddd {Blank Cell} fff T (s/b wrong) 5 ddd eee fff ddd eee fffff F The formula for column E is AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )). 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(B2,C2,D2))=2,F,T),F) As a result, all records with FALSE 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 | |||
Linear Mode
