With adavnced filter, how do I exclude a value?

[nmss18]

Hello,
I am trying to use the advanced criter filter to filter OUT a value. For example. In the table below, how do I display everything but beverages. I am using the <> to filter this out but it is not working.
Any assistance would be grateful.
Thanks,
nmss18

<form id="aspnetForm" method="post" name="aspnetForm" action="http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP010073942.aspx">Type</form>	Salesperson	Sales
<>Beverages
<form id="aspnetForm" method="post" name="aspnetForm" action="http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP010073942.aspx">Type</form>	Salesperson	Sales
Beverages	Suyama	$5,122
Meat	Davolio	$450
produce	Buchanan	$6,328
Produce	Davolio	$6,544

<tbody>
</tbody><colgroup><col span="3"></colgroup>

 

[Aladin Akyurek]

I think I have done this, but please say if there is a better solution

=IF(AND(COUNTBLANK($BG$6:$BG$12)>0,ISBLANK(BG21)),FALSE,ISNA(MATCH(BG21,$BG$6:$BG$12,0)))

Thanks

Can be shorter...

=AND(BG21<>"",ISNA(MATCH(BG21,$BG$6:$BG$12,0)))

 

[ktrasler]

I think I have done this, but please say if there is a better solution

=IF(AND(COUNTBLANK($BG$6:$BG$12)>0,ISBLANK(BG21)),FALSE,ISNA(MATCH(BG21,$BG$6:$BG$12,0)))

Thanks

Hi again

OK, little more complex now. It is working but I'm thinking there's a better way.

Now I've included a partial non match

=IF(AND(COUNTBLANK(BG$6:BG$12)>0,ISBLANK(BG21)),FALSE,IF(ISNA(LOOKUP(1E+100,SEARCH($BG$6:$BG$12,BG21),ROW($BG$6:$BG$12))),ISNA(MATCH(BG21,BG$6:BG$12,0)),FALSE))

Can this be done more efficient

Thanks

 

[Aladin Akyurek]

Hi again

OK, little more complex now. It is working but I'm thinking there's a better way.

Now I've included a partial non match

=IF(AND(COUNTBLANK(BG$6:BG$12)>0,ISBLANK(BG21)),
FALSE,IF(ISNA(LOOKUP(1E+100,SEARCH($BG$6:$BG$12,BG21),ROW($BG$6:$BG$12))),
ISNA(MATCH(BG21,BG$6:BG$12,0)),FALSE))

Can this be done more efficient

Thanks

Assuming that we want to exclude blanks, partial and full matches...

=AND(
  $BG1<>"",
  ISNA(LOOKUP(9.99E+307,SEARCH($BG$6:$BG$12,$BG21)),
  ISNA(MATCH($BG21,BG$6:BG$12,0)))

In fact, the following might suffice:

=AND(
  $BG1<>"",
  ISNA(LOOKUP(9.99E+307,SEARCH($BG$6:$BG$12,$BG21)))

Note. No need introducing a new constant like 1E+100 next to Excel's 9.99999999999999E+307, sometimes abbreviated to 9.99E+307 (see http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html).

 

[ktrasler]

Assuming that we want to exclude blanks, partial and full matches...

In fact, the following might suffice:

=AND(
  $BG1<>"",
  ISNA(LOOKUP(9.99E+307,SEARCH($BG$6:$BG$12,$BG21)))

Hi Aladin

Shouldn't the BG1 be BG21?

Thanks

 

[Aladin Akyurek]

Hi Aladin

Shouldn't the BG1 be BG21?

Thanks

Yes. Just a typo.

 

[ktrasler]

Hi Aladin, thank for this. very helpful thread.

 

[Aladin Akyurek]

Hi Aladin, thank for this. very helpful thread.

You are welcome.Thanks for the nice comment.