Advanced filter issue

naderfikry

New Member
Joined
Nov 11, 2014
Messages
2
Hi all,

I've been using advanced filter with criteria range for quite long now but lately I noticed something which I am not sure if it's a bug or not.

Please check the example belowe, I have the 1st column which I need to filter (column on the left) and the criteria range column (column on the right).

Now after filtering I expect to get the filtered result showing "John" and "Tom" only; however "John Archer" comes out as well which produces inaccurate results for me specially when I work on large data sheets. Was it always like this or is this new? Or is it a bug?

Thanks.


<colgroup><col></colgroup><tbody>
Names
Names
John John
Emma
Tom
Mary
Tom
Donald
John Archer

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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
By default, advanced filter uses a 'begins with' text filter. So if you just enter John, it will match any rows that start with John. If you want an exact match, enter:
'=John
in the criteria cell.
 

naderfikry

New Member
Joined
Nov 11, 2014
Messages
2
By default, advanced filter uses a 'begins with' text filter. So if you just enter John, it will match any rows that start with John. If you want an exact match, enter:
'=John
in the criteria cell.
Oh, Thank you very much Rory. Great tip!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help, and welcome to the forum! :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,089,970
Messages
5,411,577
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top