common characters in seperate strings
 

I have two columns of names. I would like to identify similar names
between the two columns for a possible match. For example column A
row 13 = "Epogen"; column B row 15 "Epogen / 100 mg". I would like
to indicate next to b 15 that it is a possible match to column a row
13. I tried an array formula like =IF(COUNTIF($a$8:$a
$15,b15)0,+b15,"") but that only works if there is an exact match.
Any ideas?

common characters in seperate strings
 

Are there/ will there ever be empty cells in your range A8:A15 ?

Biff

wrote in message
oups.com...
I have two columns of names. I would like to identify similar names
between the two columns for a possible match. For example column A
row 13 = "Epogen"; column B row 15 "Epogen / 100 mg". I would like
to indicate next to b 15 that it is a possible match to column a row
13. I tried an array formula like =IF(COUNTIF($a$8:$a
$15,b15)0,+b15,"") but that only works if there is an exact match.
Any ideas?

common characters in seperate strings
 

On Feb 26, 4:36 pm, "T. Valko" wrote:
Are there/ will there ever be empty cells in your range A8:A15 ?

Biff

wrote in message

oups.com...



I have two columns of names. I would like to identify similar names
between the two columns for a possible match. For example column A
row 13 = "Epogen"; column B row 15 "Epogen / 100 mg". I would like
to indicate next to b 15 that it is a possible match to column a row
13. I tried an array formula like =IF(COUNTIF($a$8:$a
$15,b15)0,+b15,"") but that only works if there is an exact match.
Any ideas?- Hide quoted text -

- Show quoted text -

The stated range will be a continous list of pharmaceuticals. (no
blank cells)

common characters in seperate strings
 

Ok....

Based on the logic of your formula you want the value in column B returned.
Try this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A$8:A$15,B15)))),B15,"")

The reason I asked about empty cells is because an empty cell evaluates as
0. So, an empty cell would match with Epogen / 100 mg since that string
contains a 0.

Biff

wrote in message
oups.com...
On Feb 26, 4:36 pm, "T. Valko" wrote:
Are there/ will there ever be empty cells in your range A8:A15 ?

Biff

wrote in message

oups.com...



I have two columns of names. I would like to identify similar names
between the two columns for a possible match. For example column A
row 13 = "Epogen"; column B row 15 "Epogen / 100 mg". I would like
to indicate next to b 15 that it is a possible match to column a row
13. I tried an array formula like =IF(COUNTIF($a$8:$a
$15,b15)0,+b15,"") but that only works if there is an exact match.
Any ideas?- Hide quoted text -

- Show quoted text -

The stated range will be a continous list of pharmaceuticals. (no
blank cells)

common characters in seperate strings
 

On Feb 27, 3:08 pm, "T. Valko" wrote:
Ok....

Based on the logic of your formula you want the value in column B returned.
Try this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A$8:A$15,B15)))),B15,"")

The reason I asked about empty cells is because an empty cell evaluates as
0. So, an empty cell would match with Epogen / 100 mg since that string
contains a 0.

Biff

wrote in message

oups.com...



On Feb 26, 4:36 pm, "T. Valko" wrote:
Are there/ will there ever be empty cells in your range A8:A15 ?

Biff

wrote in message

groups.com...

I have two columns of names. I would like to identify similar names
between the two columns for a possible match. For example column A
row 13 = "Epogen"; column B row 15 "Epogen / 100 mg". I would like
to indicate next to b 15 that it is a possible match to column a row
13. I tried an array formula like =IF(COUNTIF($a$8:$a
$15,b15)0,+b15,"") but that only works if there is an exact match.
Any ideas?- Hide quoted text -

- Show quoted text -

The stated range will be a continous list of pharmaceuticals. (no
blank cells)- Hide quoted text -

- Show quoted text -

That works great, one subtle twist, what if in Column b i come across
an abreviation of Epogen or "Epo", could I somehow expand on the
formula to catch these possibilities?

common characters in seperate strings
 

You might be able to use something like this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(LEFT(A$8:A$15,3),B15)))),B15,"")

This compares the first 3 letters of column A with the entry in B15.

Biff

wrote in message
ups.com...
On Feb 27, 3:08 pm, "T. Valko" wrote:
Ok....

Based on the logic of your formula you want the value in column B
returned.
Try this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A$8:A$15,B15)))),B15,"")

The reason I asked about empty cells is because an empty cell evaluates
as
0. So, an empty cell would match with Epogen / 100 mg since that string
contains a 0.

Biff

wrote in message

oups.com...



On Feb 26, 4:36 pm, "T. Valko" wrote:
Are there/ will there ever be empty cells in your range A8:A15 ?

Biff

wrote in message

groups.com...

I have two columns of names. I would like to identify similar names
between the two columns for a possible match. For example column A
row 13 = "Epogen"; column B row 15 "Epogen / 100 mg". I would like
to indicate next to b 15 that it is a possible match to column a row
13. I tried an array formula like =IF(COUNTIF($a$8:$a
$15,b15)0,+b15,"") but that only works if there is an exact match.
Any ideas?- Hide quoted text -

- Show quoted text -

The stated range will be a continous list of pharmaceuticals. (no
blank cells)- Hide quoted text -

- Show quoted text -

That works great, one subtle twist, what if in Column b i come across
an abreviation of Epogen or "Epo", could I somehow expand on the
formula to catch these possibilities?