Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup text values
I want to compare one column of text data with another, but not all of the
information is the same. I don't need to know whether the data is exact, I simply want to know if it is alike. Eg. A B Green Apples Apples Juicy Pears Tree If any part of the two cells are the same, e.g. as Row 1 I want to be able to return a value. Is auto filtering the only way to do this. |
#2
|
|||
|
|||
Hi
this is a little bit complicated as you have to define what is 'alike' for you. You could try for example =IF(ISNUMBER(SEARCH(B1,A1)),"B1 is in A1","no match") -- Regards Frank Kabel Frankfurt, Germany "LizJ" schrieb im Newsbeitrag ... I want to compare one column of text data with another, but not all of the information is the same. I don't need to know whether the data is exact, I simply want to know if it is alike. Eg. A B Green Apples Apples Juicy Pears Tree If any part of the two cells are the same, e.g. as Row 1 I want to be able to return a value. Is auto filtering the only way to do this. |
#3
|
|||
|
|||
You could try putting this in column C (or anywhere else).
=IF(ISERROR(SEARCH(B2,A2)),"","Match") Art |
#4
|
|||
|
|||
What are the rules, can there be multiple words in both columns like
Green Apples Pears that are green Juicy Pears Juicy Tangerines I want red apples Apple juice is awful If that's the case it basically impossible, if it is one word in B like in your example, then you already got an answer, use a third column with Frank's formula and later filter on that formula Regards, Peo Sjoblom "LizJ" wrote: I want to compare one column of text data with another, but not all of the information is the same. I don't need to know whether the data is exact, I simply want to know if it is alike. Eg. A B Green Apples Apples Juicy Pears Tree If any part of the two cells are the same, e.g. as Row 1 I want to be able to return a value. Is auto filtering the only way to do this. |
#5
|
|||
|
|||
Thank you, yes I have got multiple words in both columns, I have pasted
Frank's formula in, but it seems to give inconsistent results. e.g. Mr C Smith has matched with Mr Smith, but on another row, Mr D Findlay hasn't matched with Mr Findlay. I think I may have to do tis one by eye. "Peo Sjoblom" wrote: What are the rules, can there be multiple words in both columns like Green Apples Pears that are green Juicy Pears Juicy Tangerines I want red apples Apple juice is awful If that's the case it basically impossible, if it is one word in B like in your example, then you already got an answer, use a third column with Frank's formula and later filter on that formula Regards, Peo Sjoblom "LizJ" wrote: I want to compare one column of text data with another, but not all of the information is the same. I don't need to know whether the data is exact, I simply want to know if it is alike. Eg. A B Green Apples Apples Juicy Pears Tree If any part of the two cells are the same, e.g. as Row 1 I want to be able to return a value. Is auto filtering the only way to do this. |
#6
|
|||
|
|||
Hi
that's correct as the string 'Mr Findlay' is not (in this oder) to be found in the string 'Mr D Findlay'. What you need is a 'fuzzy pattern match'. See for example: http://tinyurl.com/4sya3 -- Regards Frank Kabel Frankfurt, Germany "LizJ" schrieb im Newsbeitrag ... Thank you, yes I have got multiple words in both columns, I have pasted Frank's formula in, but it seems to give inconsistent results. e.g. Mr C Smith has matched with Mr Smith, but on another row, Mr D Findlay hasn't matched with Mr Findlay. I think I may have to do tis one by eye. "Peo Sjoblom" wrote: What are the rules, can there be multiple words in both columns like Green Apples Pears that are green Juicy Pears Juicy Tangerines I want red apples Apple juice is awful If that's the case it basically impossible, if it is one word in B like in your example, then you already got an answer, use a third column with Frank's formula and later filter on that formula Regards, Peo Sjoblom "LizJ" wrote: I want to compare one column of text data with another, but not all of the information is the same. I don't need to know whether the data is exact, I simply want to know if it is alike. Eg. A B Green Apples Apples Juicy Pears Tree If any part of the two cells are the same, e.g. as Row 1 I want to be able to return a value. Is auto filtering the only way to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Remove the apostrophe (') in Excel cell text values | Excel Discussion (Misc queries) | |||
Stop Excel from converting text labels in CSV files to Values | Excel Discussion (Misc queries) | |||
text and values combined in one cel | Excel Discussion (Misc queries) | |||
double lookup, nest, or macro? | Excel Worksheet Functions |