I have a problem with the data I have on my worksheet and
was wondering if maybe you guys can help. I have a column
of data actually part numbers in column D on worksheet
Open P.O. that I'm matching with part numbers in column G
on another worksheet, the formula below is what I have in
place. Now the problem I'm having is that some of the part
numbers in column D on worksheet Open P.O. have a # 1 at
the end of it, this is there because the data was imported
from our system and was placed there. but the data in
column G on the other worksheet was manully typed in by
users without the # 1 at the end of the part #. These 2
part #'s are the same but when i use the formula below
it's looking for an exact match and will return a value of
false. How can I modify the formula or create a macro that
can make these distinct differences and return a value of
yes. Hope this makes sense and thanks for any help you can
offer.

=IF(ISERROR(MATCH(G2,'Open P.O.'!
$D$2:$D$30000,0)),"NO","YES")

If it helps any there is always a space and then the # 1
for the part numbers in column D on worksheet Open P.O. so
maybe a macro that will delete the ending character if it
is spaced from the remaining string?


-----Original Message-----
I have a problem with the data I have on my worksheet
and
was wondering if maybe you guys can help. I have a column
of data actually part numbers in column D on worksheet
Open P.O. that I'm matching with part numbers in column G
on another worksheet, the formula below is what I have in
place. Now the problem I'm having is that some of the
part
numbers in column D on worksheet Open P.O. have a # 1 at
the end of it, this is there because the data was
imported
from our system and was placed there. but the data in
column G on the other worksheet was manully typed in by
users without the # 1 at the end of the part #. These 2
part #'s are the same but when i use the formula below
it's looking for an exact match and will return a value
of
false. How can I modify the formula or create a macro
that
can make these distinct differences and return a value of
yes. Hope this makes sense and thanks for any help you
can
offer.

=IF(ISERROR(MATCH(G2,'Open P.O.'!
$D$2:$D$30000,0)),"NO","YES")
.

Replace your reference to G2 with

IF(MID(G2,LEN(G2)-1,1)=" ",LEFT(G2,LEN(G2)-2),G2)

which should turn out to be something like this (if I have the parentheses all correct):

=IF(ISERROR(MATCH(IF(MID(G2,LEN(G2)-1,1)=" ",LEFT(G2,LEN(G2)-2),G2),'Open
P.O.'!$D$2:$D$30000,0)),"NO","YES")


On Wed, 23 Jul 2003 15:08:25 -0700, "Brian" wrote:

If it helps any there is always a space and then the # 1
for the part numbers in column D on worksheet Open P.O. so
maybe a macro that will delete the ending character if it
is spaced from the remaining string?


-----Original Message-----
I have a problem with the data I have on my worksheet
and
was wondering if maybe you guys can help. I have a column
of data actually part numbers in column D on worksheet
Open P.O. that I'm matching with part numbers in column G
on another worksheet, the formula below is what I have in
place. Now the problem I'm having is that some of the
part
numbers in column D on worksheet Open P.O. have a # 1 at
the end of it, this is there because the data was
imported
from our system and was placed there. but the data in
column G on the other worksheet was manully typed in by
users without the # 1 at the end of the part #. These 2
part #'s are the same but when i use the formula below
it's looking for an exact match and will return a value
of
false. How can I modify the formula or create a macro
that
can make these distinct differences and return a value of
yes. Hope this makes sense and thanks for any help you
can
offer.

=IF(ISERROR(MATCH(G2,'Open P.O.'!
$D$2:$D$30000,0)),"NO","YES")
.