Find data in one table from another.
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
|