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

	A	B	C	D	E	F	G	H
1	Date	Salesrep	Sales		Date	SalesRep	SalesRep	Sales
2	3/18/2010	Fred	5			Jo	<>Joe
3	3/19/2010	Joe	5			Fred
4	3/20/2010	Jo	5
5	3/21/2010	Sue	5		Date	Salesrep	Sales
6	3/22/2010	Fred	5		3/18/2010	Fred	5
7	3/23/2010	Joe	5		3/20/2010	Jo	5
8	3/24/2010	Jo	5		3/22/2010	Fred	5
9	3/25/2010	Sue	5		3/24/2010	Jo	5
10	3/26/2010	Joe	5

<tbody>
</tbody>

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!