common characters in seperate strings

  • Thread starter Thread starter mike.klein
  • Start date Start date
M

mike.klein

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?
 
Are there/ will there ever be empty cells in your range A8:A15 ?

Biff






- Show quoted text -
Click to expand...

The stated range will be a continous list of pharmaceuticals. (no
blank cells)
 
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
 
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







- Show quoted text -
Click to expand...

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?
 
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
 
Back
Top