Doing away with helper column?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Hi,

I have two columns of text strings, one of which (col A) is just an
alphabetic string and the other (col B) is an alpha-numeric.

What I want to do is check whether each of the strings in col A is in
col B (if the numeric suffixes were removed).

What I was going to do was add a column and remove the numeric suffixes
(actually a year in brackets) with:

=LEFT(B1,FIND("(",B1,1)-2)

and then see if the COL A strings are in that column using MATCH.

My question is - Is there any way of doing the same thing but without
the need to add a column and create the new range ? Find out if each
A column string matches any cell in column B (but without the year range
in brackets (which isn't a fixed length)

Any help appreciated.....Regards, Jason
 
Combine the two formulas into one formula and use it
to Conditionally Format column B...
=MATCH(LEFT(B1,FIND("(",B1,1)-1),A:A,0)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



(Excel Add-ins / Excel Programming)
"Jay" <[email protected]>
wrote in message
Hi,
I have two columns of text strings, one of which (col A) is just an
alphabetic string and the other (col B) is an alpha-numeric.
What I want to do is check whether each of the strings in col A is in
col B (if the numeric suffixes were removed).
What I was going to do was add a column and remove the numeric suffixes
(actually a year in brackets) with:

=LEFT(B1,FIND("(",B1,1)-2)

and then see if the COL A strings are in that column using MATCH.
My question is - Is there any way of doing the same thing but without
the need to add a column and create the new range ? Find out if each
A column string matches any cell in column B (but without the year range
in brackets (which isn't a fixed length)
Any help appreciated.....Regards, Jason
 
Seeing some samples would've helped. Maybe something like this:

=ISNUMBER(MATCH(A1&"*",B$1:B$10,0))

Copied down as needed.
 
Back
Top