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. |
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. |
You could try putting this in column C (or anywhere else).
=IF(ISERROR(SEARCH(B2,A2)),"","Match") Art |
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. |
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. |
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. |
All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com