Here's a challenging question (looked everywhere on net for answer and none satifies my question).
I have bill of materials (hundreds of lines long) on worksheet #1 , which have part descriptions etc.. On worksheet #2 , I have two lists/tables: First labelled "ANYPOL" which contains specific words (full or partial), and second labelled "NOTPOL" which means key words (full or partial).
Ex. part description (which in worksheet #1 starting on column E11
WORKSHEET #1 "ANYPOL" TABLE
[TABLE="width: 215"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SOT223[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]SOT22[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
.
WORKSHEET #2 "NOTPOL" TABLE
[TABLE="width: 215"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BI-DI[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]CER[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]
IC VOLT SOT22 REG POS ADJ TRUE
IC VOLT REG SOT22 BI-DI POS ADJ FALSE
IC VOLT REG POS SOT223 ADJ FALSE
Looking in part description for any specific text (ex.: package type which is "SOT-223-4") which is on worksheet #2 first table "ANYPOL" AND any text that is not on worksheet #2 second table "NOTPOL"
So formula would look something like this
IF(AND(ANY TEXT ON ANYPOL TABLE IN PART DESC?,ANY TEXT ON NOTPOL NOT IN PART DESC?),VLOOKUP TEXT THAT IS MATCHING IN PART DESC TO ANYPOL TABLE THEN RETURN VALUE FROM 2ND COLUMN IN ANYPOL TABLE ("TRUE OR FALSE","FALSE")
Now SOT22 is in description and also in ANYPOL but not in NOTPOL table so return result is "TRUE". Notice how I put ANYPOL table sorted from Z to A so it didn't match to SOT223 and give "FALSE" result?
IC VOLT SOT22 REG POS ADJ
Now SOT22 is in ANYPOL and BI-DI is in NOTPOL tables so logic will be false for AND statement and automatically return "FALSE" from VLOOKUP when logic fails.
IC VOLT REG SOT22 BI-DI POS ADJ
Finally, SOT22 is in description and also in ANYPOL but not in NOTPOL table so return result is "FALSE" from ANYPOL 2nd column.
IC VOLT REG POS SOT223 ADJ
So far this is the formula I have but not working properly:
=IF((AND((SUMPRODUCT(--ISNUMBER(SEARCH(ANYPOL,E11)))>0)=TRUE,SUMPRODUCT(--ISNUMBER(SEARCH(NOTPOL,E11)))>0)<>TRUE),VLOOKUP("*"&ANYPOL&"*",'DO NOT DELETE-MODIFY'!A2:B1963,2,0))
What is wrong with my formula please?
I have bill of materials (hundreds of lines long) on worksheet #1 , which have part descriptions etc.. On worksheet #2 , I have two lists/tables: First labelled "ANYPOL" which contains specific words (full or partial), and second labelled "NOTPOL" which means key words (full or partial).
Ex. part description (which in worksheet #1 starting on column E11
WORKSHEET #1 "ANYPOL" TABLE
[TABLE="width: 215"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SOT223[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]SOT22[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
.
WORKSHEET #2 "NOTPOL" TABLE
[TABLE="width: 215"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BI-DI[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]CER[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]
IC VOLT SOT22 REG POS ADJ TRUE
IC VOLT REG SOT22 BI-DI POS ADJ FALSE
IC VOLT REG POS SOT223 ADJ FALSE
Looking in part description for any specific text (ex.: package type which is "SOT-223-4") which is on worksheet #2 first table "ANYPOL" AND any text that is not on worksheet #2 second table "NOTPOL"
So formula would look something like this
IF(AND(ANY TEXT ON ANYPOL TABLE IN PART DESC?,ANY TEXT ON NOTPOL NOT IN PART DESC?),VLOOKUP TEXT THAT IS MATCHING IN PART DESC TO ANYPOL TABLE THEN RETURN VALUE FROM 2ND COLUMN IN ANYPOL TABLE ("TRUE OR FALSE","FALSE")
Now SOT22 is in description and also in ANYPOL but not in NOTPOL table so return result is "TRUE". Notice how I put ANYPOL table sorted from Z to A so it didn't match to SOT223 and give "FALSE" result?
IC VOLT SOT22 REG POS ADJ
Now SOT22 is in ANYPOL and BI-DI is in NOTPOL tables so logic will be false for AND statement and automatically return "FALSE" from VLOOKUP when logic fails.
IC VOLT REG SOT22 BI-DI POS ADJ
Finally, SOT22 is in description and also in ANYPOL but not in NOTPOL table so return result is "FALSE" from ANYPOL 2nd column.
IC VOLT REG POS SOT223 ADJ
So far this is the formula I have but not working properly:
=IF((AND((SUMPRODUCT(--ISNUMBER(SEARCH(ANYPOL,E11)))>0)=TRUE,SUMPRODUCT(--ISNUMBER(SEARCH(NOTPOL,E11)))>0)<>TRUE),VLOOKUP("*"&ANYPOL&"*",'DO NOT DELETE-MODIFY'!A2:B1963,2,0))
What is wrong with my formula please?
). That's it for bad words list
