Doing away with helper column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 18 Jul 2007 00:47 GMT

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

Reply to this Message

Jim Cone - 18 Jul 2007 01:27 GMT

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)

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

(Excel Add-ins / Excel Programming)
"Jay" <spam@dummyaddress.spam.com>
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

Reply to this Message

T. Valko - 18 Jul 2007 01:30 GMT

Seeing some samples would've helped. Maybe something like this:

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

Copied down as needed.

Signature

Biff
Microsoft Excel MVP

> Hi,
>
[quoted text clipped - 17 lines]
>
> Any help appreciated.....Regards, Jason

Reply to this Message