| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for partial text match in range
I am trying to search a partial text match given a range, in this case
three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for partial text match in range
Try this:
=COUNTIF(B$2:D$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")0 Biff wrote in message oups.com... I am trying to search a partial text match given a range, in this case three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for partial text match in range
Biff,
If there anyway to modify it so that it will only search for the last name from Column A. So essentially everything up until the comma, but nothing after? because this returns 0 for everything still. Thanks for your help. Biff wrote: Try this: =COUNTIF(B$2:D$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")0 Biff wrote in message oups.com... I am trying to search a partial text match given a range, in this case three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for partial text match in range
Just remove the wildcards "*"
=COUNTIF(B$2:D$9,LEFT(A2,FIND(",",A2)-1))0 Biff wrote in message oups.com... Biff, If there anyway to modify it so that it will only search for the last name from Column A. So essentially everything up until the comma, but nothing after? because this returns 0 for everything still. Thanks for your help. Biff wrote: Try this: =COUNTIF(B$2:D$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")0 Biff wrote in message oups.com... I am trying to search a partial text match given a range, in this case three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Match and Sort two range of data? | New Users to Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Trying to match a text string to a data table, any suggestions? | Excel Worksheet Functions | |||
| Find last occurance of text in range | Excel Worksheet Functions | |||
| Use MATCH to find position of max in 2D range? | Excel Worksheet Functions | |||
Linear Mode
