Advanced Filter not doing what I want it to do.

[completeexcelnoob]

Hey, currently doing Slaying Excel Dragons homework problem 84 which involves using advanced filter. As you can see, I want to set the criteria to Jo OR Fred but the result is also including "Joe" which is Jo plus an "e". Why is excel doing this and how do I stop it?

 

[Weazel]

you can add an AND criteria of <> Joe

and Mike talks about the issue here https://www.youtube.com/watch?v=bxlzRQC6Wfw around the 4 minute mark

Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Date[/TD]
[TD]Salesrep[/TD]
[TD]Sales[/TD]
[TD="align: right"][/TD]
[TD]Date[/TD]
[TD]SalesRep[/TD]
[TD]SalesRep[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]3/18/2010[/TD]
[TD]Fred[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Jo[/TD]
[TD]<>Joe[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]3/19/2010[/TD]
[TD]Joe[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Fred[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3/20/2010[/TD]
[TD]Jo[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]3/21/2010[/TD]
[TD]Sue[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD]Date[/TD]
[TD]Salesrep[/TD]
[TD]Sales[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]3/22/2010[/TD]
[TD]Fred[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/18/2010[/TD]
[TD]Fred[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]3/23/2010[/TD]
[TD]Joe[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/20/2010[/TD]
[TD]Jo[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]3/24/2010[/TD]
[TD]Jo[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/22/2010[/TD]
[TD]Fred[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]3/25/2010[/TD]
[TD]Sue[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/24/2010[/TD]
[TD]Jo[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]3/26/2010[/TD]
[TD]Joe[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet6​

 

[completeexcelnoob]

Interesting, Thanks!

 

[mikerickson]

Another solutions would be to explicitly say "equals" by putting a leading =.

SalesRep
=Jo
=Fred

You will probably have to use a leading apostrophe to avoid a #NAME error '=Jo

 

[completeexcelnoob]

Thanks Mikerickson, works perfectly!