Re: Find data in one table from another.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <micmed@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:7538C0B1-3B5B-47B4-920D-4F024A32016A@xxxxxxxxxxxxxxxx 
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" <micmed@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:75283699-7E73-409E-A396-4A4CE01D5D84@xxxxxxxxxxxxxxxx
> 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
>> > >
>> > >



.

Relevant Pages