Word Search With Exceptions

[dwgnome]

Trying to search for keywords but need to exclude certain combinations.

For the following example:


Inventory

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 331px"><COL style="WIDTH: 58px"><COL style="WIDTH: 58px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>K</TD><TD>W</TD><TD>X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3030</TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Paint</TD><TD style="BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #33cccc; TEXT-ALIGN: left">x</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3031</TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Urethane</TD><TD style="BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #33cccc; TEXT-ALIGN: left">x</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3032</TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Paint Thinner</TD><TD style="BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #33cccc; TEXT-ALIGN: left">x</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3033</TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Urethane</TD><TD style="BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #33cccc; TEXT-ALIGN: left">x</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3034</TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Urethane Remover</TD><TD style="BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #33cccc; TEXT-ALIGN: left">x</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3035</TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Latex Caulk</TD><TD style="BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #33cccc; TEXT-ALIGN: left">x</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>X3030</TD><TD>=IF(AND(OR(ISNUMBER(SEARCH({"Tough","Enamel","Latex"," Urethane","Primer","Paint"},K3030)),NOT(ISNUMBER(SEARCH({"Stripper","Thinner","Remover","Caulk"},K3030))))),"x","")</TD></TR><TR><TD>X3031</TD><TD>=IF(AND(OR(ISNUMBER(SEARCH({"Tough","Enamel","Latex"," Urethane","Primer","Paint"},K3031)),NOT(ISNUMBER(SEARCH({"Stripper","Thinner","Remover","Caulk"},K3031))))),"x","")</TD></TR><TR><TD>X3032</TD><TD>=IF(AND(OR(ISNUMBER(SEARCH({"Tough","Enamel","Latex"," Urethane","Primer","Paint"},K3032)),NOT(ISNUMBER(SEARCH({"Stripper","Thinner","Remover","Caulk"},K3032))))),"x","")</TD></TR><TR><TD>X3033</TD><TD>=IF(AND(OR(ISNUMBER(SEARCH({"Tough","Enamel","Latex"," Urethane","Primer","Paint"},K3033)),NOT(ISNUMBER(SEARCH({"Stripper","Thinner","Remover","Caulk"},K3033))))),"x","")</TD></TR><TR><TD>X3034</TD><TD>=IF(AND(OR(ISNUMBER(SEARCH({"Tough","Enamel","Latex"," Urethane","Primer","Paint"},K3034)),NOT(ISNUMBER(SEARCH({"Stripper","Thinner","Remover","Caulk"},K3034))))),"x","")</TD></TR><TR><TD>X3035</TD><TD>=IF(AND(OR(ISNUMBER(SEARCH({"Tough","Enamel","Latex"," Urethane","Primer","Paint"},K3035)),NOT(ISNUMBER(SEARCH({"Stripper","Thinner","Remover","Caulk"},K3035))))),"x","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Where the cell has the word Thinner or Remover, I do not want the "x" to be placed in Column X.

My formula with NOT does not appear to be working.

Any help would be appreciated.

 

[dwgnome]

Please Help!

 

[Aladin Akyurek]

Not sure if I need to create a new thread but when I convert the array of exceptions (ExceptionsP) and allowed words (PaintKeyWords) to a named range, and if there are any empty cells, the answer comes out wrong. If all of the cells are full, the formula works fine.

Is there a way to set up the formula to allow blank cells (empty) or create a dynamic range?

=IF(AND((SUMPRODUCT(ISNUMBER(SEARCH([COLOR=navy]PaintKeyWords[/COLOR],K3040))*1))=0,(SUMPRODUCT(ISNUMBER(SEARCH([COLOR=navy]ExceptionsP[/COLOR],K3040))*1))=0),"",IF(((SUMPRODUCT(ISNUMBER(SEARCH([COLOR=navy]ExceptionsP[/COLOR],K3040))*1)>0)*1)>=1,"","x"))

Where: PaintKeyWords = J3047:J3052
ExceptionsP = K3047:K3052

Any help would be appreciated.

Create a new sheet named KeyWords and enter the following lists:

In column A, from A2 on:
<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=75><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2673" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=75>Tough</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Enamel</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Latex</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Urethane</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Primer</TD></TR>
<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2829718 class=xl63 height=20>Paint</TD></TR></TBODY></TABLE>

In column B, from B2 on:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Stripper</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Thinner</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Remover</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Caulk</TD></TR></TBODY></TABLE>

Define BigStr by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=REPT("z",255)

Define PaintKeyWords as referring to:

=KeyWords!$A$2:INDEX(KeyWords!$A:$A,MATCH(BigStr,KeyWords!$A:$A))

Define ExceptionsP as referring to:

=KeyWords!$B$2:INDEX(KeyWords!$B:$B,MATCH(BigStr,KeyWords!$B:$B))

and BigNum as referring to:

=9.99999999999999E+307

Now we can simply invoke:

=IF(ISNUMBER(LOOKUP(BigNum,SEARCH(ExceptionsP,K3040))),"",
   IF(ISNUMBER(LOOKUP(BigNum,SEARCH(PaintKeyWords,K3040))),"x",""))

 

[dwgnome]

Hi,

Thank you for the excellent suggestion. Following exactly your directions, the formula worked. When I added words to the Paint and Exceptions column it worked beautifully.

However, a weird thing happens when I remove one or more of the original words in either column on sheet Keyword. If I remove the word Paint, the result still shows an "x" in the column, even after hitting F9 to recalculate. Also "x" appears where none were before removing the word Paint. There should not be an "x" because none of the keywords appear in the Manufacturer Description column (column K where the phrases are located).

Can this be explained?

 

[Aladin Akyurek]

Hi,

Thank you for the excellent suggestion. Following exactly your directions, the formula worked. When I added words to the Paint and Exceptions column it worked beautifully.

As intended...

However, a weird thing happens when I remove one or more of the original words in either column on sheet Keyword. If I remove the word Paint, the result still shows an "x" in the column, even after hitting F9 to recalculate. Also "x" appears where none were before removing the word Paint. There should not be an "x" because none of the keywords appear in the Manufacturer Description column (column K where the phrases are located).

Can this be explained?

SEARCH succeeds always when the length of the search string is zero. An empty cell means a 0-lenght search string.


Deleting/clearing the contents of an arbitrary cell in ExceptionsP and/or PaintKeyWords as you do creates an empty cell, hence the adverse effects.

Definitions as given above cannot prevent empty cells created by clearing cells in between the start and end rows. The maintenance of these lists (additions and or deletions) are expected to occur only from their end.

In order to robustify the definitions against arbitrary changes, we can do the following.

We keep the definitions of BigStr and BigNum that we already have.

We remove the KeyWords sheet.

We create two new sheets instead, named pkeywords and ekeywords.

Let column A in pkeywords house the paint list from A1 on:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Tough</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Enamel</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Latex</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Urethane</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Primer</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Paint</TD></TR></TBODY></TABLE>

Define PLIST as referring to:

=INDEX(pkeywords!$A:$A,1,1):INDEX(pkeywords!$A:$A,MATCH(BigStr,pkeywords!$A:$A))

Define PaintKeyWords as referring to:

=IF(PLIST="","###",PLIST)

Let column A on ekeywords house the exception list from A1 on:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Stripper</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Thinner</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Remover</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Caulk</TD></TR></TBODY></TABLE>

Define ELIST as referring to:

=INDEX(ekeywords!$A:$A,1,1):INDEX(ekeywords!$A:$A,MATCH(BigStr,ekeywords!$A:$A))

Define ExceptionsP as referring to:

=IF(ELIST="","###",ELIST)

The foregoing dynamic range definitions are robust against:

1) additions at both ends;
2) deletions from both ends;
3) deleing/clearing the contents of the arbitrary cells.

and allow us to invoke the formula we already have:

=IF(ISNUMBER(LOOKUP(BigNum,SEARCH(ExceptionsP,K3040))),"",
   IF(ISNUMBER(LOOKUP(BigNum,SEARCH(PaintKeyWords,K3040))),"x",""))

 

[dwgnome]

Aladin,

Thank you again for stepping in on this. I never thought that "dynamic named ranges" would be so complex. I am away from being able to try your suggestions, but will continue when I get back.

Again thank so much for taking the time to explain.

 

[Aladin Akyurek]

Aladin,

Thank you again for stepping in on this. I never thought that "dynamic named ranges" would be so complex. I am away from being able to try your suggestions, but will continue when I get back.

Again thank so much for taking the time to explain.

You are welcome.