compare cell contents

G

guy

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

guy

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,A2,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 said:
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,A2,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!!!
Click to expand...
 
G

guy

Please ignore the previous 2 posts... sorry.

guy said:
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,A2,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 said:
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,A2,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!!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare cells in different worksheets 3
COMPARE CELL CONTENTS 4
List to Table - formula 2
Find data in columns, then place in rows 2
Difficult Formula 9
Countif with a Range of criteria 8
Countif Range not working 4
condionally formatting based on another cells formatting? 2

Top