Hi everyone, In this video, I show you how to unlock the power of the Advanced Filter using AND and OR in the criteria. If you have any questions, please leave your comments below. A quick guide: T* - all cells that start with T. T - all cells that start with T. *Y - all cells that end with Y. ="=Tom" - all cells that match exactly Tom. ="Tom" - all cells that do not match exactly Tom.
Excel Macro Mastery can you make a video how to create an udf filter function using vba like the new array formula filter in office 365 so that the users with earlier version of office can use it too
Thank you sir. (1:13 of this video) i want to filter just by typing (an or nn) for all Ann. From the vba code instead of typing (*?) in the cell. Eg: i'm using in another code for your ref (Field:=4, Criteria1:="*" & [A2] & "*") Which is used to filter all the matched range including before and after the search criteria.
Hi Paul.. another useful and informative video. Learning a lot about both VBA and basic use of native Advanced Filter.. excellent! Thanks and keep them coming. Love your videos, tutorials, blog posts, web site, etc. Excel Macro Mastery is the place to go to learn it right. Thumbs up!!
very informative. In my case there are over 200 columns of data and need to filter on few columns, and the output should be generated with few specific columns and not all 200. is this possible with advanced filter?
Thanks Paul, immensely useful and very well presented. Never new about using a formula with Advanced Filter, makes it even more powerful than I thought. Would love to see some advanced stuff on web scraping with VBA so I can get the data set in the first place. Please keep the videos coming. :)
This is great. How can I do the same thing but filter for each name in one run and paste them on top of each other, so each sales person has their own section with headers? Thanks
Thank you for this very informative video. I am struggling to get the advanced filter to work with a date range. I tried stacking the criteria on subsequent rows, such as “>09/10/2019” then on the row below “
Thank you again for the video. I am just wondering if I could ask you a question regarding Excel, What I'm trying to do is to give a value to a particular cell in a formula based on a condition being true in another cell, some thing look like this, if cell('a1')=1 then cell('a2").value = A else nothing happens. Is it possible to have this done in Excel without using VBA?
Love the content, as a suggestion for another video. I would love to see how or if you can use conditional formatting in combination with Advanced filters and how that is done in VBA. To for example highlight fields that include Tom yellow and sales above 20.000 in Green etcs.
To filter between two dates, you need an AND. In the criteria range enter to columns with the name and the dates. NB tested with >01-Jan-2019, not number only dates, so American formatting may mix things up, Filter for a year Day Day >=01/01/2019
Hi everyone, Great video again, thanks paul hope you can help me. I am using a table (ListObject) as a Advance Filter criteria range for another larger database. in this table, for the AND criteria, I wish to have two identical header names, but a "2" gets automatically added to each new duplicate header name, like so .. duplicate of "Surname" becomes "Surname2". For AND criteria to work, the header names need to be exactly the same. How do you get Excel VBA to permit duplicate header row names ?
Hi Paul, thanks for the amazing and very well presented video. but i have a question, how to filter where the criteria are not equal to? I can filter using if it's a single criteria, but didn't work on multiple criteria (OR) which look like this : Header something something thanks.
Can you use this to say filter for when Tom or Ann, have quantities 5,7 or 9? I’m thinking if you put Tom in B2, Ann in B3. 5 in C2, 7 in C3 and 9 in C4. You’d miss the record where Ann sells 5?
@@Excelmacromastery thank you, though this could get long quite quickly. My real life example could have 10 in one column, and 10 in another. Meaning 100 rows.
Hi Everyone, ! Thank you for amazing videos. I am struggeling with changing lists of just one criteria. Like I would apply lists to group the entries . I have created different Lists in the NameManager but struggle to run :"for i (got to this list for advFilter, next i (next list) etc. I am stuck. Thank you
yours teaching way is very unique..sir thnx...today I enjoyed yours if and or topic video class there r some issue with me plz cn u hlp me ..I do watching with you product criteria range true,false graterthen 20000 but there values not change how is possible I dnt know cn u hlp me..plz if it is like you then plz make a video with excel sheet on whitout vba.. plz
FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)-says this is for arrays (is it Adv Filter)? Excel VBA Copy - The Complete Guide to Copying Data: (excelmacromastery.com/excel-v...) This is a link to the Array article
Hi Jerry, I have update the second link which was wrong. Thanks for pointing that out. The cheat sheet for arrays is correct. I didn't have one for Advanced filter when I created the video. You can see the quick guide here: excelmacromastery.com/vba-advanced-filter/
Hi everyone,
In this video, I show you how to unlock the power of the Advanced Filter using AND and OR in the criteria.
If you have any questions, please leave your comments below.
A quick guide:
T* - all cells that start with T.
T - all cells that start with T.
*Y - all cells that end with Y.
="=Tom" - all cells that match exactly Tom.
="Tom" - all cells that do not match exactly Tom.
Excel Macro Mastery can you make a video how to create an udf filter function using vba like the new array formula filter in office 365 so that the users with earlier version of office can use it too
Please add examples in filtering dates.
Thank you sir. (1:13 of this video) i want to filter just by typing (an or nn) for all Ann. From the vba code instead of typing (*?) in the cell.
Eg: i'm using in another code for your ref
(Field:=4, Criteria1:="*" & [A2] & "*") Which is used to filter all the matched range including before and after the search criteria.
Hi Paul.. another useful and informative video. Learning a lot about both VBA and basic use of native Advanced Filter.. excellent! Thanks and keep them coming. Love your videos, tutorials, blog posts, web site, etc. Excel Macro Mastery is the place to go to learn it right. Thumbs up!!
Thanks very much Wayne. I appreciate the feedback and support. Plenty more on the way.
Paul, today I have watched / I reviewed some of your videos and always learned something new. Thank you very much.
I really liked these videos of using the advanced filter in VBA. This tutorial is very useful and helpful. Thanks indeed Paul.
You're welcome Salim.
Thanks for this video Paul. I’ve learned a lot from it.
Thanks for your comment.
SUPER! MANY THANKS FOR YOUR TOPTIP
very informative. In my case there are over 200 columns of data and need to filter on few columns, and the output should be generated with few specific columns and not all 200. is this possible with advanced filter?
Another gem mate, keep Em coming
Thanks:-)
Thanks Paul, immensely useful and very well presented.
Never new about using a formula with Advanced Filter, makes it even more powerful than I thought.
Would love to see some advanced stuff on web scraping with VBA so I can get the data set in the first place.
Please keep the videos coming.
:)
Thanks Jack. I have a web scraping application in my Excel VBA Handbook Course
Just found your channel Paul. Loving it, keep up the great work 👍🏻
Thanks a lot Graham.
Thank you so much, Paul. It's very brilliant.
Great video, plz keep it doing regular
Plenty more to come.
This is great. How can I do the same thing but filter for each name in one run and paste them on top of each other, so each sales person has their own section with headers? Thanks
Excellent Paul - the Criteria is similar to ACCESS queries.
Thanks John. Yes, it's very similar.
Thank you very much, an I hope to see more. You are amazing, great teaching skill.
Thanks for your nice comment Graham.
Very nice! That is what I'm looking for.
Happy to help.
Is it possible to download this codes?
Thank you for this very informative video.
I am struggling to get the advanced filter to work with a date range. I tried stacking the criteria on subsequent rows, such as “>09/10/2019” then on the row below “
Thank you again for the video. I am just wondering if I could ask you a question regarding Excel, What I'm trying to do is to give a value to a particular cell in a formula based on a condition being true in another cell, some thing look like this, if cell('a1')=1 then cell('a2").value = A else nothing happens. Is it possible to have this done in Excel without using VBA?
Hey Paul nice to see you on youtube. I didn't know that you have a youtube channel. I just knew about your website.
Thanks for finding and support my channel Imran.
00:00 - Introduction
01:06 - Using AND criteria
01:47 - Using OR Criteria
02:46 - Using Formulas in Criteria
05:03 - Using Wildcards
Thank you. What about calculated filed after adv. filter copy to another sheet. Is there any way?
yes, just watch the previous video
Hi! Thanks for your video! Can you share your excel Copy?
Love the content, as a suggestion for another video. I would love to see how or if you can use conditional formatting in combination with Advanced filters and how that is done in VBA. To for example highlight fields that include Tom yellow and sales above 20.000 in Green etcs.
Thanks for the suggestion Niels. I'll keep it in mind
Thanks Paul.
You're Welcome.
Could you Show ist how to use IT in Userform with Text or Comboboxen?
To filter between two dates, you need an AND. In the criteria range enter to columns with the name and the dates. NB tested with >01-Jan-2019, not number only dates, so American formatting may mix things up, Filter for a year
Day Day
>=01/01/2019
Sir, How to filter values of one row having 10 columns with many other rows having values in 25 columns.
how to use RegEXtext in advance search to allow searching for embedded text?
Hi everyone,
Great video again, thanks paul
hope you can help me. I am using a table (ListObject) as a Advance Filter criteria range for another larger database. in this table, for the AND criteria, I wish to have two identical header names, but a "2" gets automatically added to each new duplicate header name, like so .. duplicate of "Surname" becomes "Surname2". For AND criteria to work, the header names need to be exactly the same. How do you get Excel VBA to permit duplicate header row names ?
how would you use the "and/or" and formulas if you were using the UseAdvancedFilterCopy code?
Could you figure it out?
Can u show examples in filtering dates
It looks like after some number of criteria modifications Advanced filter becomes painfully slow. Any idea why?
How do you filter for a single letter stock symbol for AT&T for example is T - Thanks great videos!
="=T"
@@Excelmacromastery Thank you :-)
Hi Paul, thanks for the amazing and very well presented video.
but i have a question, how to filter where the criteria are not equal to?
I can filter using if it's a single criteria, but didn't work on multiple criteria (OR) which look like this :
Header
something
something
thanks.
="Tom"
you need to add as a AND condition, not an OR. Like in
Header,Header
something,something
Hi,
It's really nice technique.. if possible can you make video on visual studio.. bcz I feel in TH-cam there is no proper content on visual studio.
Thanks Susanta. I'm just focusing on VBA for the moment.
Can you use this to say filter for when Tom or Ann, have quantities 5,7 or 9?
I’m thinking if you put Tom in B2, Ann in B3. 5 in C2, 7 in C3 and 9 in C4. You’d miss the record where Ann sells 5?
Tom 5
Tom 7
Tom 9
Ann 5
Ann 7
Ann 9
@@Excelmacromastery thank you, though this could get long quite quickly. My real life example could have 10 in one column, and 10 in another. Meaning 100 rows.
Hi Everyone, !
Thank you for amazing videos.
I am struggeling with changing lists of just one criteria. Like I would apply lists to group the entries . I have created different Lists in the NameManager but struggle to run :"for i (got to this list for advFilter, next i (next list) etc.
I am stuck.
Thank you
hi a thank you for sharing the channel to help me e with a
You're welcome
How to filter date. If I put *2020-06 it will not work.
💘💯
yours teaching way is very unique..sir thnx...today I enjoyed yours if and or topic video class there r some issue with me plz cn u hlp me ..I do watching with you product criteria range true,false graterthen 20000 but there values not change how is possible I dnt know cn u hlp me..plz if it is like you then plz make a video with excel sheet on whitout vba..
plz
Advanced filter applies the formula to all the row similar to how you copy a a formula to different rows.
Merci
You're welcome Essaid.
We want moooooore.
Thanks Robert. Plenty more on the way.
I m traing the advance filter but I cnt do ds like u ohk
Thanks Vijay
FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)-says this is for arrays (is it Adv Filter)?
Excel VBA Copy - The Complete Guide to Copying Data: (excelmacromastery.com/excel-v...) This is a link to the Array article
Hi Jerry, I have update the second link which was wrong. Thanks for pointing that out.
The cheat sheet for arrays is correct. I didn't have one for Advanced filter when I created the video. You can see the quick guide here: excelmacromastery.com/vba-advanced-filter/
ignore my message
solution has been found