COMPARE CELL CONTENTS

[guy]

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,A2,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!!!

 

[Peo Sjoblom]

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

 

[M C Del Papa]

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]

thanks!

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

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,A2,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!!!

 

[guy]

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(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

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,A2,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!!!