matching columns using text strings!

[via135 via OfficeKB.com]

hi!

i am having text data thru A1:B6

col A ...............col B
john ambrose....ambrose j
edmond c.........c edmon
arthur a............morais p
w peter william..peter
williams john.....w john
ambrose...........rose j

i need to compare the lists and shortlist the rows
having the unmatched columns with some flag in col C.

the criteria is if some text string *minimum with 5 characters*
in either column should be matched with the other column!

thus the result should be as under:

arthur a..................morais p
ambrose.................rose j

hope that explained the things well!

any help pl?

-via135

 

[JMB]

you could try

=OR(ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2)-4)),5),A2)))
array entered (Cntrl+Shift+Enter) in cell C2 and copy it down.

It won't pick up on the transposition of the first name initial (if that is
the deciding character)

for example:
"c edmo" would not match "edmo c" because only 4 consecutive letters would
match.

 

[JMB]

BTW spaces are counted as a character. I think the spaces could be removed,
but then

would not match. Also, changing the spaces to an "*" (wildcard search)
won't work because then (for example)


ambrose...........rose*
would match


one other possibility you could try is to see if the next to last character
is a space (for both A2 and B2), such as
edmond c ......... c edmo
and move the last two characters to the front so that it becomes
c edmond ......... c edmo
which would then match.

=OR(ISNUMBER(SEARCH(MID(IF(MID(B2,LEN(B2)-1,1)=" ",RIGHT(B2,1)&"
"&LEFT(B2,LEN(B2)-2),B2),ROW(INDIRECT("1:"&LEN(B2)-4)),5),IF(MID(A2,LEN(A2)-1,1)=" ",RIGHT(A2,1)&" "&LEFT(A2,LEN(A2)-2),A2))))

array entered w/Cntrl+Shift+Enter