ExcelBanter - View Single Post

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