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.

 

[rassten]

Is it something like this you want

Excel Workbook
	A	B	C
1	Paint		x
2	Urethane		x
3	Paint Thinner
4	Urethane		x
5	Urethane Remover
6	Latex Caulk
Ark1

 

[dwgnome]

Thank you rassten, that is a huge improvement. However, when I have the phrase "Stain Remover" or just "Stripper" in the cell it still gives me an "x", where there should be none.

Any way to work around that?

 

[dwgnome]

Just realized that using rassten's suggested formula results in any words not related to the core phrases of "paint", "enamel", "latex", "primer", "paint" will result in an "x". Even "Pink Lady" is marked as "x". I only want an "x" if any of the words contain the core phrases, minus the exceptions as noted above.

Any ideas?

 

[rassten]

Sorry,
try this

Excel Workbook
	A	B	C
2	Paint		x
3	Urethane		x
4	Paint Thinner
5	Urethane		x
6	Urethane Remover
7	Latex Caulk
8	Stain Remover
9	Stripper
10	Pink Lady
Ark1

 

[dwgnome]

That worked perfectly!

Thank you for your help.

 

[dwgnome]

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.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump!