I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

Hi

You probably need to add the optional 4th argument to Vlookup, of either
FALSE or 0.
This is necessary when the data is not sorted alphabetically.
C1: =VLOOKUP(A1,CATALOG,2,0)


--
Regards

Roger Govier


"micmed" wrote in message
...
I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989)
that
contains text with the data from a column (A1:A32417) that contains a
list of
part numbers and display a result(True/False) into another column (C).
Column
B's text has the part numbers mixed in with the text. I need to know
which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\

If there is the possibility of multiple matches, let us know.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

If I understand your posting correctly, you are saying that you have
something like this in column A:

1234
567
8914
23456

i.e. part numbers - you do not state if these are in order or if they
follow a particular format (eg 6 digit). In column B you have text
which contains the part number, something like:

1234 wheel
567 passenger door
8914 headlamp
23456 rear bumper

Again, you give us no examples of what this is like - are the part
numbers always to the left, or can they be embedded within the text?
Are they always separated from the text by at least one space?

If this is a fair description of your problem, then VLOOKUP won't work
on its own. But, in order to give you some further help you do need to
describe your data a bit more fully with examples.

Pete

Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
Ron's returns FALSE for all rows in column A. I tried both on cells that I
knew did and did not exist in CATALOG. Any other ideas?

"Ron Coderre" wrote:

Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\

If there is the possibility of multiple matches, let us know.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

Thanks for asking Pete. The part numbers do not follow any particular format.
They could be:
12345
34-5678
FL-975
3K-23G-85
WPL-1176

Some of the part numbers are embedded, some are to the left and all are
seperated by at least one space.

WPL-1176 €¢ $39.99
6 Ft. FWC-9-9-6 . . . . . . . . . . . . . . . . . . . . . . . . . $44.9510 Ft.
FW6-FW6CM FWA-6F-4M FWA-6M-4F FW-6J-4P FW6-BLCM

"Pete" wrote:

If I understand your posting correctly, you are saying that you have
something like this in column A:

1234
567
8914
23456

i.e. part numbers - you do not state if these are in order or if they
follow a particular format (eg 6 digit). In column B you have text
which contains the part number, something like:

1234 wheel
567 passenger door
8914 headlamp
23456 rear bumper

Again, you give us no examples of what this is like - are the part
numbers always to the left, or can they be embedded within the text?
Are they always separated from the text by at least one space?

If this is a fair description of your problem, then VLOOKUP won't work
on its own. But, in order to give you some further help you do need to
describe your data a bit more fully with examples.

Pete

I just tested my formulas with your posted data formats.

Just to reiterate:
You have a part number list in Col_A.
You have a catalog list in Col_B of the same sheet.
You want to know if each part number in Col_A exists on any line in the
catalog.

If that Is that correct, then both formulas in C1 and copied down worked fine.

I suspect that something else is impacting your results. Perhaps the
references need to be adjusted.


***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
Ron's returns FALSE for all rows in column A. I tried both on cells that I
knew did and did not exist in CATALOG. Any other ideas?

"Ron Coderre" wrote:

Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\

If there is the possibility of multiple matches, let us know.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

Does it matter that some of the cells in Col_A have a warning stating the
number is stored as text? I have tried changing all of Col_A to General but
that doasn't get rid of the warning.

"Ron Coderre" wrote:

I just tested my formulas with your posted data formats.

Just to reiterate:
You have a part number list in Col_A.
You have a catalog list in Col_B of the same sheet.
You want to know if each part number in Col_A exists on any line in the
catalog.

If that Is that correct, then both formulas in C1 and copied down worked fine.

I suspect that something else is impacting your results. Perhaps the
references need to be adjusted.


***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
Ron's returns FALSE for all rows in column A. I tried both on cells that I
knew did and did not exist in CATALOG. Any other ideas?

"Ron Coderre" wrote:

Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\

If there is the possibility of multiple matches, let us know.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Kostis is correct...I wasn't aware that a part number could appear in the
CATALOG range more than once and I didn't allow for that in my formula.

This one allows for mutiple occurrences:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
copy down as far as needed.

(Nice catch, Kostis)
***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Does it matter that some of the cells in Col_A have a warning stating the
number is stored as text? I have tried changing all of Col_A to General but
that doasn't get rid of the warning.

"Ron Coderre" wrote:

I just tested my formulas with your posted data formats.

Just to reiterate:
You have a part number list in Col_A.
You have a catalog list in Col_B of the same sheet.
You want to know if each part number in Col_A exists on any line in the
catalog.

If that Is that correct, then both formulas in C1 and copied down worked fine.

I suspect that something else is impacting your results. Perhaps the
references need to be adjusted.


***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
Ron's returns FALSE for all rows in column A. I tried both on cells that I
knew did and did not exist in CATALOG. Any other ideas?

"Ron Coderre" wrote:

Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\

If there is the possibility of multiple matches, let us know.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989

Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought to
what you must have and post back with the new rules. That way you can get one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

How about

=COUNTIF(CATALOG,A1)0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Oops! I never read the first post, so B holds other data as well in the same
cell and if so is there any rule where A1 can be, is it first or last or
anywhere?



--
Regards,

Peo Sjoblom

Portland, Oregon




"Peo Sjoblom" wrote in message
...
How about

=COUNTIF(CATALOG,A1)0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is 602,
I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

What I need to know is if the part number in Col_A can or cannot be found in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678 Yes
FL-975 Green 25-FL-975 No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176 No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought to
what you must have and post back with the new rules. That way you can get one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Sorry about Col_C wrapping around.

"micmed" wrote:

What I need to know is if the part number in Col_A can or cannot be found in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678 Yes
FL-975 Green 25-FL-975 No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176 No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought to
what you must have and post back with the new rules. That way you can get one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

It worked for me using the example you provided, what does not work? Using
your own example I got

FALSE
TRUE
TRUE
FALSE
TRUE



Of course I assumed that CATALOG is a named range and the values are in A,
if not CATALOG is a named range and your parts are in column C replace
CATALOG with

=(COUNTIF(C:C,A1)+COUNTIF(C:C,"* "&A1&" *")+COUNTIF(C:C,A1&"
*")+COUNTIF(C:C,"* "&A1))0

this formula looks for if the value in A is the only value in the lookup
range, it looks if it is part of the range where it is spaces to the left
and to the right, it looks if it starts with it and then space before the
rest and finally if it ends with it with space preceding it

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be
found
in
its exact form within the text of Array CATALOG. I do not want a
positive
return if the part number text has anything immediately before or
after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus
Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some
thought
to
what you must have and post back with the new rules. That way you can
get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the
formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula.
The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in
CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A1&" "," "&CATALOG&" ")))0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

If your part number test must be case-sensitive, meaning that
AB123 does not match Ab123, then use this:

=SUMPRODUCT(--ISNUMBER(FIND(" "&$A1&" "," "&CATALOG&" ")))0

Helping, yet?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A1&" "," "&CATALOG&" ")))0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Ron, you are the man! That did it perfectly. Now I just have to wait for it
to calculate.

"Ron Coderre" wrote:

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A1&" "," "&CATALOG&" ")))0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides

Thanks for the feedback...I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Ron, you are the man! That did it perfectly. Now I just have to wait for it
to calculate.

"Ron Coderre" wrote:

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A1&" "," "&CATALOG&" ")))0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides