excel advanced filter does not contain text

**refrigman** · 11-08-2012, 05:01 PM

I am trying to do an advanced filter by doing a does not contain "xxx". Here is a simplified example.

Service Requested	Service Performed
seal	test1
frost	test2
test3	seal
test4	frost
board6	test5
test6	board1
test7	test8
seal	board2
frost	board3
board4	seal
board5	frost

I would to filter both Service Requested & Service Performed by the cells that contain either seal or frost in either column, but do not contain board. This is the table for the advanced filter that I have been using with no luck:

Service Requested	Service Performed	Service Requested	Service Performed
seal		<>Board
frost			<>Board
	seal	<>Board
	frost		<>Board

The end result would look like this:

Service Requested	Service Performed
seal	test1
frost	test2
test3	seal
test4	frost

How do I use the does not contain "text" in the advanced filter?

**rylo** · 11-08-2012, 10:22 PM

Hi

Raw data in range A1:B12
Criteria in range E1:F5
Criteria Data
E1: Requested
E2: ="*seal*"
E3: ="*frost*"
E4: ="<>"&"*board*"
E5: ="<>"&"*board*"
F1: Service Performed
F2: ="<>"&"*board*"
F3: ="<>"&"*board*"
F4: ="*seal*"
F5: ="*frost*"

Copy to Range J1:K1 (which have the appropriate headings)

See how that goes.

rylo

**FDibbins** · 11-08-2012, 11:10 PM

if you're data is in columns A and B, use a helper column in C and copy this down...
=IF(OR(NOT(ISERROR(SEARCH("board",A2,1))),NOT(ISERROR(SEARCH("board",B2,1)))),"",
IF(OR(A2="seal",A2="frost",B2="seal",B2="frost"),1,""))
filter on C