Today's question from Bill.
Did you know that Excel's Advanced Filter is returning partial matches?
A phrase in the criteria range is treated as Phrase*.
This is an annoying problem.
Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work.
In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work.
This all begs the question: Why am I trying to use Advanced Filter at all? How about IFERROR(SIGN(MATCH))) and filter that column to 1?
Table of Contents
(0:00) Advanced Filter Review
(0:50) Bug Filter returns partial matches
(1:18) Reading Excel Help
(1:35) Formula in Criteria
(2:09) 100K rows, 250 criteria phrases
(2:40) Build formula with ^ instead of =
(3:12) Replace ^ with =
(3:37) Use MATCH instead?
Did you know that Excel's Advanced Filter is returning partial matches?
A phrase in the criteria range is treated as Phrase*.
This is an annoying problem.
Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work.
In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work.
This all begs the question: Why am I trying to use Advanced Filter at all? How about IFERROR(SIGN(MATCH))) and filter that column to 1?
Table of Contents
(0:00) Advanced Filter Review
(0:50) Bug Filter returns partial matches
(1:18) Reading Excel Help
(1:35) Formula in Criteria
(2:09) 100K rows, 250 criteria phrases
(2:40) Build formula with ^ instead of =
(3:12) Replace ^ with =
(3:37) Use MATCH instead?
Transcript of the video:
Hey, Microsoft: why is Advanced Filter using partial matching?
All right.
So ,we're talking about the old, old Advanced Filter here. I have a bunch of phrases.
I am looking for these three phrases under Data, Advanced Filter.
I want to filter in place. Here's the criteria range.
It has the same heading. I'm getting apple, banana, and fig.
Click okay. And I understand it's not case-sensitive.
So I expect to get apple and capital APPLE.
But the beautiful thing about this is I can now fill in these matching sales. Control+V.
And I can come over here, type Bill, Ctrl+Enter. And enter some more info here.
And then, when I clear the filter, see, I've updated just the matching rows.
That's how it's supposed to work.
But I'm trying this in real life, and here are three phrases that I'm going to match. There are only two matches in this entire thing.
And when I go to Advanced Filter, click okay, it's giving me things that don't match.
I'm looking for apples, and it's giving me applesauce is delicious.
How to split Excel cell, and I'm also getting how to split Excel cell into two.
I went up to their Help topic on this. And I'm not using any of these wild cards.
But they're certainly treating it like I have that text followed by an asterisk.
And then, I also saw in that Help topic where they say that it needs to look like equal, quote, equal, the word you're looking for, end quote. All right.
And when I try and build that with this awesome little formula here to get it to look like what they want it to look like and try the Advanced Filter, it doesn't work, right?
The one thing that does work, this is so annoying, is actually creating formulas that start with an equal sign, then a quote, then an equal sign, the text from over here, and the quote.
But for me to create this, I had to do a formula, convert it to values, and then add the equal sign in.
And let's talk about the real life scenario. It's not this little small eight rows.
It's a hundred thousand phrases.
And I'm searching for 250 specific phrases in the criteria. And this is in Excel Online.
I'm collaborating with 20 others. I'm trying not to screw up their view.
So, I click Edit in Desktop. Turn AutoSave off.
Create the criteria range for the 250. Apply the Advanced Filter.
Fill columns B, C, and D. Clear the Advanced Filter.
Remove the criteria range. Do a full Save.
And then stop editing and return to Excel online. And hope that I haven't hacked anyone off.
Did you know that Advanced Filter is doing partial matching from the beginning?
Do you have a better workaround than this horrible workaround where I use a formula to put a caret.
Quote, that requires quote quote.
Equal sign, the text, and then, at the end, four quotes there to put in one quote.
Take this, Ctrl+C, Ctrl+Shift+V to convert the formulas to values.
And then, Ctrl+H and change the caret to an equal sign, Replace All, click okay, click close.
Now, I end up with the formula that looks like they want it to look like.
And if I come out here, Advanced Filter, then I get the two things that actually match, not partial matching.
Let me know down below if you have a better way.
Maybe I should just abandon the Advanced Filter, put the Criteria values in. Use this in a new column O to get zeros or ones.
Paste values. Filter that column to the 1s.
Fill B, C, and D. Clear O and, clear Q.
And just be done with it.
All right, well, hey, I want to thank me for sending that question in. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
All right.
So ,we're talking about the old, old Advanced Filter here. I have a bunch of phrases.
I am looking for these three phrases under Data, Advanced Filter.
I want to filter in place. Here's the criteria range.
It has the same heading. I'm getting apple, banana, and fig.
Click okay. And I understand it's not case-sensitive.
So I expect to get apple and capital APPLE.
But the beautiful thing about this is I can now fill in these matching sales. Control+V.
And I can come over here, type Bill, Ctrl+Enter. And enter some more info here.
And then, when I clear the filter, see, I've updated just the matching rows.
That's how it's supposed to work.
But I'm trying this in real life, and here are three phrases that I'm going to match. There are only two matches in this entire thing.
And when I go to Advanced Filter, click okay, it's giving me things that don't match.
I'm looking for apples, and it's giving me applesauce is delicious.
How to split Excel cell, and I'm also getting how to split Excel cell into two.
I went up to their Help topic on this. And I'm not using any of these wild cards.
But they're certainly treating it like I have that text followed by an asterisk.
And then, I also saw in that Help topic where they say that it needs to look like equal, quote, equal, the word you're looking for, end quote. All right.
And when I try and build that with this awesome little formula here to get it to look like what they want it to look like and try the Advanced Filter, it doesn't work, right?
The one thing that does work, this is so annoying, is actually creating formulas that start with an equal sign, then a quote, then an equal sign, the text from over here, and the quote.
But for me to create this, I had to do a formula, convert it to values, and then add the equal sign in.
And let's talk about the real life scenario. It's not this little small eight rows.
It's a hundred thousand phrases.
And I'm searching for 250 specific phrases in the criteria. And this is in Excel Online.
I'm collaborating with 20 others. I'm trying not to screw up their view.
So, I click Edit in Desktop. Turn AutoSave off.
Create the criteria range for the 250. Apply the Advanced Filter.
Fill columns B, C, and D. Clear the Advanced Filter.
Remove the criteria range. Do a full Save.
And then stop editing and return to Excel online. And hope that I haven't hacked anyone off.
Did you know that Advanced Filter is doing partial matching from the beginning?
Do you have a better workaround than this horrible workaround where I use a formula to put a caret.
Quote, that requires quote quote.
Equal sign, the text, and then, at the end, four quotes there to put in one quote.
Take this, Ctrl+C, Ctrl+Shift+V to convert the formulas to values.
And then, Ctrl+H and change the caret to an equal sign, Replace All, click okay, click close.
Now, I end up with the formula that looks like they want it to look like.
And if I come out here, Advanced Filter, then I get the two things that actually match, not partial matching.
Let me know down below if you have a better way.
Maybe I should just abandon the Advanced Filter, put the Criteria values in. Use this in a new column O to get zeros or ones.
Paste values. Filter that column to the 1s.
Fill B, C, and D. Clear O and, clear Q.
And just be done with it.
All right, well, hey, I want to thank me for sending that question in. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
