Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
LizJ
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Art
 
Posts: n/a
Default

You could try putting this in column C (or anywhere else).

=IF(ISERROR(SEARCH(B2,A2)),"","Match")

Art
  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
LizJ
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 01:53 AM
Remove the apostrophe (') in Excel cell text values Connull Excel Discussion (Misc queries) 5 January 11th 05 06:06 PM
Stop Excel from converting text labels in CSV files to Values Just Want a Label! Excel Discussion (Misc queries) 1 January 11th 05 05:51 PM
text and values combined in one cel Bart Excel Discussion (Misc queries) 1 December 14th 04 09:36 AM
double lookup, nest, or macro? Josef.angel Excel Worksheet Functions 1 October 29th 04 09:50 AM


All times are GMT +1. The time now is 10:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"