software

Please click the link below:

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 amp; 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(B2amp;C2amp;D2)))gt;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 (gt;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!!!
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(B3amp;C3amp;D3)))gt;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

quot;guyquot; gt; wrote in message
...
gt; Please click the link below:
gt;
gt; cjoint.com/data/mxvW30Cruc.htm
gt;
gt; The formula for column E is
gt; AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )).
gt;
gt; First, why the result shows #VALUE! What's wrong with the formula?
gt;
gt; The goal is to check the combination of [first name, middle name and last
gt; name] against the signature. But sometimes the signature can be considered
gt; as correct (TRUE, as in E3 amp; E4) even if it is wrong. What's tricky is we
gt; don't need the signature to be exactly the same as the combination of the
3
gt; names, so I can't use EXACT function. For example, what's calculated as
gt; FALSE (E5) will be filtered and manually checked to see if the spelling
gt; mistakes are acceptable or not.So I changed the above formula to
gt;
IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(
B2amp;C2amp;D2)))gt;2,FALSE,TRUE),FALSE)
gt; As a result, all records with FALSE or #VALUE! need further manual
checking.
gt;
gt; 1. As the number of records is very large (gt;10000 records), the manual
gt; checking precedure takes a extremely long time. Can anyone suggest better
gt; ways to do the task? Thanks!
gt;
gt; 2. There are a number of keywords forbidden to use in the signature, say
the
gt; list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL?
gt;
gt; Thanks a lot!!!!!
gt; Thanks!!!
gt;
gt;
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 Papaquot;guyquot; gt; wrote in message
...
gt; Please click the link below:
gt;
gt; cjoint.com/data/mxvW30Cruc.htm
gt;
gt; The formula for column E is
gt; AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )).
gt;
gt; First, why the result shows #VALUE! What's wrong with the formula?
gt;
gt; The goal is to check the combination of [first name, middle name and last
gt; name] against the signature. But sometimes the signature can be considered
gt; as correct (TRUE, as in E3 amp; E4) even if it is wrong. What's tricky is we
gt; don't need the signature to be exactly the same as the combination of the
gt; 3
gt; names, so I can't use EXACT function. For example, what's calculated as
gt; FALSE (E5) will be filtered and manually checked to see if the spelling
gt; mistakes are acceptable or not.So I changed the above formula to
gt; IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(B2amp;C2amp;D2)))gt;2,FALSE,TRUE),FALSE)
gt; As a result, all records with FALSE or #VALUE! need further manual
gt; checking.
gt;
gt; 1. As the number of records is very large (gt;10000 records), the manual
gt; checking precedure takes a extremely long time. Can anyone suggest better
gt; ways to do the task? Thanks!
gt;
gt; 2. There are a number of keywords forbidden to use in the signature, say
gt; the
gt; list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL?
gt;
gt; Thanks a lot!!!!!
gt; Thanks!!!
gt;
gt;
thanks!
quot;M C Del Papaquot; gt; s.prodigy.net...
gt; Guy,
gt;
gt; First, there is nothing wrong with the formula. SEARCH returns #VALUE! if
gt; the text is not found. Try consulting the online help for this function
gt; for further clarification. If you want to suppress the error then you can
gt; use an IF...THEN to test if an error is returned to set the error to a
gt; value of your choice like FALSE.
gt;
gt; I can't really comment on the latter portion of your post because I
gt; struggle to understand what you are trying to accomplish.
gt;
gt; M C Del Papa
gt;
gt;
gt; quot;guyquot; gt; wrote in message
gt; ...
gt;gt; Please click the link below:
gt;gt;
gt;gt; cjoint.com/data/mxvW30Cruc.htm
gt;gt;
gt;gt; The formula for column E is
gt;gt; AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )).
gt;gt;
gt;gt; First, why the result shows #VALUE! What's wrong with the formula?
gt;gt;
gt;gt; The goal is to check the combination of [first name, middle name and last
gt;gt; name] against the signature. But sometimes the signature can be
gt;gt; considered
gt;gt; as correct (TRUE, as in E3 amp; E4) even if it is wrong. What's tricky is we
gt;gt; don't need the signature to be exactly the same as the combination of the
gt;gt; 3
gt;gt; names, so I can't use EXACT function. For example, what's calculated as
gt;gt; FALSE (E5) will be filtered and manually checked to see if the spelling
gt;gt; mistakes are acceptable or not.So I changed the above formula to
gt;gt; IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(B2amp;C2amp;D2)))gt;2,FALSE,TRUE),FALSE)
gt;gt; As a result, all records with FALSE or #VALUE! need further manual
gt;gt; checking.
gt;gt;
gt;gt; 1. As the number of records is very large (gt;10000 records), the manual
gt;gt; checking precedure takes a extremely long time. Can anyone suggest better
gt;gt; ways to do the task? Thanks!
gt;gt;
gt;gt; 2. There are a number of keywords forbidden to use in the signature, say
gt;gt; the
gt;gt; list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL?
gt;gt;
gt;gt; Thanks a lot!!!!!
gt;gt; Thanks!!!
gt;gt;
gt;gt;
gt;
gt;
thanks!
quot;Peo Sjoblomquot; gt; bl...
gt; This will take care of the value errors
gt;
gt; =AND(ISNUMBER(SEARCH(B3,A3)),ISNUMBER(SEARCH(C3,A3 )),ISNUMBER(SEARCH(D3,A3))
gt; ,(LEN(A3)-SUM(LEN(B3amp;C3amp;D3)))gt;2)
gt;
gt; however since I don't know what the premises are for what is correct and
gt; incorrect I don't know about the rest, I assume you could test for empty
gt; cells etc
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;guyquot; gt; wrote in message
gt; ...
gt;gt; Please click the link below:
gt;gt;
gt;gt; cjoint.com/data/mxvW30Cruc.htm
gt;gt;
gt;gt; The formula for column E is
gt;gt; AND(Search(B2,A2,1),Search(C2,A2,1),Search(D2,A2,1 )).
gt;gt;
gt;gt; First, why the result shows #VALUE! What's wrong with the formula?
gt;gt;
gt;gt; The goal is to check the combination of [first name, middle name and last
gt;gt; name] against the signature. But sometimes the signature can be
gt;gt; considered
gt;gt; as correct (TRUE, as in E3 amp; E4) even if it is wrong. What's tricky is we
gt;gt; don't need the signature to be exactly the same as the combination of the
gt; 3
gt;gt; names, so I can't use EXACT function. For example, what's calculated as
gt;gt; FALSE (E5) will be filtered and manually checked to see if the spelling
gt;gt; mistakes are acceptable or not.So I changed the above formula to
gt;gt;
gt; IF(AND(SEARCH(B2,A2,1),SEARCH(C2,A2,1),SEARCH(D2,A 2,1)),IF((LEN(A2)-SUM(LEN(
gt; B2amp;C2amp;D2)))gt;2,FALSE,TRUE),FALSE)
gt;gt; As a result, all records with FALSE or #VALUE! need further manual
gt; checking.
gt;gt;
gt;gt; 1. As the number of records is very large (gt;10000 records), the manual
gt;gt; checking precedure takes a extremely long time. Can anyone suggest better
gt;gt; ways to do the task? Thanks!
gt;gt;
gt;gt; 2. There are a number of keywords forbidden to use in the signature, say
gt; the
gt;gt; list {ONE, TWO, THREE, FOUR....FIFTY}. Can I check this in EXCEL?
gt;gt;
gt;gt; Thanks a lot!!!!!
gt;gt; Thanks!!!
gt;gt;
gt;gt;
gt;
gt;