Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching columns using text strings!
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 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching columns using text strings!
you could try
=OR(ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B 2)-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. "via135 via OfficeKB.com" wrote: 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 -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching columns using text strings!
BTW spaces are counted as a character. I think the spaces could be removed,
but then williams john.....w john 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 "JMB" wrote: you could try =OR(ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B 2)-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. "via135 via OfficeKB.com" wrote: 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 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TEXT TO COLUMNS WITH LEADING ZEROS | Excel Discussion (Misc queries) | |||
Text Color Change prompted by different columns | Excel Worksheet Functions | |||
Text to Columns from drop down list update | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Text To Columns | Excel Worksheet Functions |