In today's dueling Excel podcast, the question is how to delete all the records which contain either ski or swim. This requires multiple wildcards in a filter. Mike and Bill will compare various methods in Episode 1090.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! I'm Bill Jelen from MrExcel.com and I've got a cool excel tip for you, today.
Hey! This is Mike Gel Girvin from Excel ls Fun on YouTube and I have a different way to do that.
Hey, all right.
It's Friday that means time for another dueling Excel podcast.
I'm Bill Jelen from learn Excel.
Joined later by Mike Girvin from Excel Is Fun.
Today, question sent in by YouTube.
Someone wants to figure out how to delete, any time the swim or ski appears in a column and it can be any version or variant of swim.
So, swimming or skiing or swimwear.
We want to delete all of those.
So, I'm gonna take a look at how to do this with filter and then with advanced filter.
Now, in the filter drop-down, of course we can pick specific things.
But I want to go to the text filters and you see we have things like contains or does not contain.
Now, both of these are going to lead to the same spot, the custom filter.
So, we'll go to contains and now we get back to what we had in Excel 2003.
The custom option, under auto filter.
I can say that it contains ski or contains swim.
Now, it's interesting that in the old version of Excel.
We didn't have contains, you had to have said that you want to use equals and then put an asterisk before and after.
Now, these are exactly equivalent items.
So, we click [ ok ] and you see that we get just the swimming and skiing items.
Now, we can select those right click and delete row, to let the entire sheet row.
That's what I told you to do click [ ok ] and when we clear the filter we have just the non-skiing and swimming items, alright!
So, that's one way to go.
Kind of a hassle to go through and set that up all the time.
So, I want to show you something called an advanced filter.
Now, in the advanced field we're going to have a criteria range.
The Criteria range up here is going to say that, hey! We're looking for star swim star the next row that's an or star, ski, star and now, we can come here and click advanced and say we're going to filter the list in place.
The Criteria range is going to be this heading plus the two things we're looking for.
Click [ ok ] and now we have just the swimming and skiing and I can delete the records just like before.
Now, here's an interesting one.
Let's make a copy of this and we'll clear that filter.
What if I wanted to show the things that aren't swimming and skiing?
That way, I could copy those.
Well, this is really wild, when you change this to be not equals to.
If you've ever heard of anything called Boolean logic, when you have a NOT it needs to change the ANDs to ORs and the ORs to ANDs.
If we would have used this criteria range right here, click advanced, click [ ok ].
It doesn't filter anything out.
The trick is because the reason it doesn't filter anything out, let me explain.
This is when it gets to swimming, it says oh well, this should be shown because it's not ski and when it gets to skiing it should be shown because it's not swim.
So, we need to take this data heading and copy it over to column F and then we'll cut and paste.
So, now we've joined these two things by OR.
Hey! Check this word is it not swimming or is it not skiing?
And if either of those are true, then it'll be shown.
So, we come back here to our advanced filter, will change this criteria range.
Change the Criteria range to be two columns by two rows, click [ ok ] and now we get the things that are not skiing and swimming.
We can copy those and go to a brand new spot and get just those items.
Hey! We'll send it over to Mike.
Mike: Thanks, MrExcel.
Wow! I hope you guys had your notepad out.
That was an amazing lesson in filtering.
Hey! You got to see how in earlier versions, we used equals and then the wild card.
In more recent versions, you could use contain.
You got to see advanced filter.
This was an OR criteria.
So, we find swim or ski and then over here totally brilliant fun.
This is an AND criteria because it's on the same row.
Each record to keep has to be NOT ski and NOT swim.
Wow! Totally amazing.
I'm going to do a formula here.
Come over here, and we're actually going to use the SEARCH function.
Let's see how the search function works.
We're going to tell it to find some text like, swim.
[ F4 ] to lock it comma within this other text string.
Now SEARCH right now will deliver one because swim starts at the first character.
But down here, go swimming, get one two three four.
So, we'll deliver a four.
So, really this function is going to deliver an error or a number.
So, we'll use that pattern to our advantage later on, now oh!
But let's we didn't get ski right.
So, let's come up here.
I'm going to use the same trick, MrExcel did.
I'm going to highlight both of these and I'm going to highlight them and hit the [ F9 ] key.
[ F9 ] key will convert and actually type out the array syntax for you.
So, you don't have to do it.
Now, what's nice is you don't have to remember the curly brackets contain the array that semicolons, rows and commas or column.
Now, let's see if this work.
I'm going to [ ctrl enter ] double click and send it down. Oh!
But it, huh, it didn't pick up ski.
Let's try this, what if we double-click and since this, right here.
This argument is the one that's given us the trouble.
It's expecting a single text string.
We gave it to, so it makes an array.
If we do [ ctrl shift enter ], that is not going to help either.
Really, what we need, notice the curly brackets because I did [ ctrl shift enter ].
What we need is to put, search.
The search results inside of a function that can interpret the array part of this.
The function to do that is LOOKUP.
LOOKUP now, look up is a function like some product that can handle arrays without [ ctrl shift enter ].
So, but we need to give it a lookup value.
Remember this search function is only going to return the number of characters at the position where our text we're looking for, starts.
Well, if we give it a lookup value of one more than the maximum number of characters.
This will work just fine.
So, 2 carat 15 (2^15) Now 2 ^ 15, that's one more character.
That'll just cover everything.
So, we'll always find a number.
Whatever number search spits out.
I'm going to close parenthesis.
Now, I got to taste them, this formula right there.
I saw this for the first time at the MrExcel message board.
Add a challenge, that MrExcel posted.
It's actually a contest and Houdini posted this formula.
Totally awesome.
Now, let's we don't even have to [ ctrl shift enter ].
All we have to do, is [ ctrl enter ] and double click and send it down.
Now, there we have it.
We have 1, 1, we have all the numbers.
We have numbers in N/A.
Not available.
Now, I want to point out something about the 2 to the 15th.
When I first saw this.
I was like, what how do I know.
That's the maximum number of characters.
I went over here and did 2 to the 15 and sure enough.
It's 32768 but then I did the REPEAT function.
I said hey, please repeat the explanation point to the 15 times.
I want to do, it returned an error.
It said forget you, I know that.
That's too many characters, but then I did 2 carat 15 minus 1, to repeat that and sure enough it worked.
It showed all the characters.
So, that's just a way that I proved to myself that that number actually is is the number of characters one more than is allowed.
Now, What do we need to do here?
Well, two things if you want to true for the ski, skiing, swim, then we're going to take this range of N/As and one and put is number.
That will give a true, where there a number is.
If you wanted the true for the N/A.
You'd use is you use the ISNA, right there.
But I'm going to use IS NUMBER.
ISNA and ISNUMBER, just looks at whatever it is and says true, or false.
I'm going to [ ctrl enter ], double click and send it down.
There we have it.
We have our truths for the ones.
We want to delete, if we'd used ISNA this would deliver a true.
I'm going to click in one cell and sort, right click, sort, right clicking, sorting is in 2007 not in earlier versions.
Earlier version, just go to the data menu or data ribbon.
So, I sorted.
I got all the trues at the bottom.
I better move this one up here before I delete.
Now, I can just highlight the rows, right just like that and right click, [ delete ], [ okay ], and so there is one way to do it with a formula. Alright!
We'll see you next trick.
MrExcel: Mike! That is a beautiful formula, excellent.
I love those formulas to confirm the MrExcel message board.
Houdini and Aladdin and other folks like that.
Just to point out to people that I took some of those best formulas and put them in a book called Excel Gurus Gone Wild.
So, if you like the trick that Mike showed and want to learn more about wild formulas like that.
Formula are frankly will make your head spin.
Check out that book at your local bookstore.
Hey! On behalf of Mike and myself, thank you for stopping by.
We'll see you next time for another dueling Excel podcast.
Hey! This is Mike Gel Girvin from Excel ls Fun on YouTube and I have a different way to do that.
Hey, all right.
It's Friday that means time for another dueling Excel podcast.
I'm Bill Jelen from learn Excel.
Joined later by Mike Girvin from Excel Is Fun.
Today, question sent in by YouTube.
Someone wants to figure out how to delete, any time the swim or ski appears in a column and it can be any version or variant of swim.
So, swimming or skiing or swimwear.
We want to delete all of those.
So, I'm gonna take a look at how to do this with filter and then with advanced filter.
Now, in the filter drop-down, of course we can pick specific things.
But I want to go to the text filters and you see we have things like contains or does not contain.
Now, both of these are going to lead to the same spot, the custom filter.
So, we'll go to contains and now we get back to what we had in Excel 2003.
The custom option, under auto filter.
I can say that it contains ski or contains swim.
Now, it's interesting that in the old version of Excel.
We didn't have contains, you had to have said that you want to use equals and then put an asterisk before and after.
Now, these are exactly equivalent items.
So, we click [ ok ] and you see that we get just the swimming and skiing items.
Now, we can select those right click and delete row, to let the entire sheet row.
That's what I told you to do click [ ok ] and when we clear the filter we have just the non-skiing and swimming items, alright!
So, that's one way to go.
Kind of a hassle to go through and set that up all the time.
So, I want to show you something called an advanced filter.
Now, in the advanced field we're going to have a criteria range.
The Criteria range up here is going to say that, hey! We're looking for star swim star the next row that's an or star, ski, star and now, we can come here and click advanced and say we're going to filter the list in place.
The Criteria range is going to be this heading plus the two things we're looking for.
Click [ ok ] and now we have just the swimming and skiing and I can delete the records just like before.
Now, here's an interesting one.
Let's make a copy of this and we'll clear that filter.
What if I wanted to show the things that aren't swimming and skiing?
That way, I could copy those.
Well, this is really wild, when you change this to be not equals to.
If you've ever heard of anything called Boolean logic, when you have a NOT it needs to change the ANDs to ORs and the ORs to ANDs.
If we would have used this criteria range right here, click advanced, click [ ok ].
It doesn't filter anything out.
The trick is because the reason it doesn't filter anything out, let me explain.
This is when it gets to swimming, it says oh well, this should be shown because it's not ski and when it gets to skiing it should be shown because it's not swim.
So, we need to take this data heading and copy it over to column F and then we'll cut and paste.
So, now we've joined these two things by OR.
Hey! Check this word is it not swimming or is it not skiing?
And if either of those are true, then it'll be shown.
So, we come back here to our advanced filter, will change this criteria range.
Change the Criteria range to be two columns by two rows, click [ ok ] and now we get the things that are not skiing and swimming.
We can copy those and go to a brand new spot and get just those items.
Hey! We'll send it over to Mike.
Mike: Thanks, MrExcel.
Wow! I hope you guys had your notepad out.
That was an amazing lesson in filtering.
Hey! You got to see how in earlier versions, we used equals and then the wild card.
In more recent versions, you could use contain.
You got to see advanced filter.
This was an OR criteria.
So, we find swim or ski and then over here totally brilliant fun.
This is an AND criteria because it's on the same row.
Each record to keep has to be NOT ski and NOT swim.
Wow! Totally amazing.
I'm going to do a formula here.
Come over here, and we're actually going to use the SEARCH function.
Let's see how the search function works.
We're going to tell it to find some text like, swim.
[ F4 ] to lock it comma within this other text string.
Now SEARCH right now will deliver one because swim starts at the first character.
But down here, go swimming, get one two three four.
So, we'll deliver a four.
So, really this function is going to deliver an error or a number.
So, we'll use that pattern to our advantage later on, now oh!
But let's we didn't get ski right.
So, let's come up here.
I'm going to use the same trick, MrExcel did.
I'm going to highlight both of these and I'm going to highlight them and hit the [ F9 ] key.
[ F9 ] key will convert and actually type out the array syntax for you.
So, you don't have to do it.
Now, what's nice is you don't have to remember the curly brackets contain the array that semicolons, rows and commas or column.
Now, let's see if this work.
I'm going to [ ctrl enter ] double click and send it down. Oh!
But it, huh, it didn't pick up ski.
Let's try this, what if we double-click and since this, right here.
This argument is the one that's given us the trouble.
It's expecting a single text string.
We gave it to, so it makes an array.
If we do [ ctrl shift enter ], that is not going to help either.
Really, what we need, notice the curly brackets because I did [ ctrl shift enter ].
What we need is to put, search.
The search results inside of a function that can interpret the array part of this.
The function to do that is LOOKUP.
LOOKUP now, look up is a function like some product that can handle arrays without [ ctrl shift enter ].
So, but we need to give it a lookup value.
Remember this search function is only going to return the number of characters at the position where our text we're looking for, starts.
Well, if we give it a lookup value of one more than the maximum number of characters.
This will work just fine.
So, 2 carat 15 (2^15) Now 2 ^ 15, that's one more character.
That'll just cover everything.
So, we'll always find a number.
Whatever number search spits out.
I'm going to close parenthesis.
Now, I got to taste them, this formula right there.
I saw this for the first time at the MrExcel message board.
Add a challenge, that MrExcel posted.
It's actually a contest and Houdini posted this formula.
Totally awesome.
Now, let's we don't even have to [ ctrl shift enter ].
All we have to do, is [ ctrl enter ] and double click and send it down.
Now, there we have it.
We have 1, 1, we have all the numbers.
We have numbers in N/A.
Not available.
Now, I want to point out something about the 2 to the 15th.
When I first saw this.
I was like, what how do I know.
That's the maximum number of characters.
I went over here and did 2 to the 15 and sure enough.
It's 32768 but then I did the REPEAT function.
I said hey, please repeat the explanation point to the 15 times.
I want to do, it returned an error.
It said forget you, I know that.
That's too many characters, but then I did 2 carat 15 minus 1, to repeat that and sure enough it worked.
It showed all the characters.
So, that's just a way that I proved to myself that that number actually is is the number of characters one more than is allowed.
Now, What do we need to do here?
Well, two things if you want to true for the ski, skiing, swim, then we're going to take this range of N/As and one and put is number.
That will give a true, where there a number is.
If you wanted the true for the N/A.
You'd use is you use the ISNA, right there.
But I'm going to use IS NUMBER.
ISNA and ISNUMBER, just looks at whatever it is and says true, or false.
I'm going to [ ctrl enter ], double click and send it down.
There we have it.
We have our truths for the ones.
We want to delete, if we'd used ISNA this would deliver a true.
I'm going to click in one cell and sort, right click, sort, right clicking, sorting is in 2007 not in earlier versions.
Earlier version, just go to the data menu or data ribbon.
So, I sorted.
I got all the trues at the bottom.
I better move this one up here before I delete.
Now, I can just highlight the rows, right just like that and right click, [ delete ], [ okay ], and so there is one way to do it with a formula. Alright!
We'll see you next trick.
MrExcel: Mike! That is a beautiful formula, excellent.
I love those formulas to confirm the MrExcel message board.
Houdini and Aladdin and other folks like that.
Just to point out to people that I took some of those best formulas and put them in a book called Excel Gurus Gone Wild.
So, if you like the trick that Mike showed and want to learn more about wild formulas like that.
Formula are frankly will make your head spin.
Check out that book at your local bookstore.
Hey! On behalf of Mike and myself, thank you for stopping by.
We'll see you next time for another dueling Excel podcast.
