Trevor, My good man, I'm 79 years old and don't like to code when I can pick and choose. Therefore, I really like your presentation as it is going to save me some wear and tear on my cogs. Keep up the good work. I'm getting myself MSO 2021 on DVD for Christmas. I'm a retired coder of many languages, but I'm amazed as what you can do with Excel and Open Office.
THANK YOU!!!!! After literally WEEKS of researching, online training and assistance from the experts at Mr Excel, this tutorial has FINALLY resolved my issue of creating a filter for my data. This has made it so easy to follow along, and really easy to understand. You are amazing, and have saved me SO MUCH TIME!!!!!
I remember watching this video a long time ago and lost it, and after some time wanted to get back to it and could not find it. Fortunately today it happened and it saved the day. Thank you for making this video and sharing the knowledge.
This video not just provides example of an advanced filter, but also other concepts such as macros, assigning buttons to macros to filter and clear data, and functions such as Offset. Very informative.
Hi Trevor, This session was extremely useful. I was trying more complicated way of coding with combo box, drop down and was not so successful. Thanks a lot for keeping it simple yet so useful.
العفو منك لا اجيد الانجليزية بشقيها كتابتاً أو نطقاً ولكن استمتعت من طريقة شرحك على الفيديو وإظهار ما تقوم به من خلال تقريب الشاشة , لذا وجب عليا شكرك لنشر العلم وايصالة لمن لا يفهم الانجليزية بشكل بيسط مع خالص تحياتي من اليمن
I have to thank you from the bottom of my heart for this tutorial! I have searched high and low to look for what i needed and this perfectly explains precisely what I needed! If I could shake your hand in person I would!!!!! THANK YOU! :)
Really helped me this video, by the way sir I want to see my last available row in filterd sheet for which I might want to scroll down,, is der any technique for that
Trevor, Is there a limit to how many Criteria can be in the Criteria Block? I'm finding that your example is limited to 4 criteria - I can't get the Advanced Filter to go beyond this!
Hi Philip, There is no limit but bear in mind that the criteria are mutually inclusive. ( eg. 2 sets of numbers or dates need specific attention) Sometimes because of that you may need to run a couple of separate filters. Best wishes Trev
Trevor Easton Thanks for your reply.I'm guessing that the Criteria Block that you've shown, with all criteria on the one row, is a series of AND formulas. However, if a new row of criteria is added to the block then we have a series of OR formulas? This additional set of criteria on an additional row works better for me.
Hi Trevor, Super helpful tutorial! I do have one question though. I am using this to filter a larger data set to display data only related to a specific rep (Sales Rep Name is my List and Criteria). I noticed, that if that field is left blank, and we click the Filter Data button, it pulls in everything. I want to prevent my users from doing that. Is there anyway to restrict this so that there must be something added to the criteria field, or a way to restrict it from pulling the entire data set?
Awesome video as usual. I can't get the Dates to work. In the criteria boxes for the 2 dates, I am putting the code in exactly as yours, making changes to the cells that are in my spreadsheet, but for some reason, the filterme macro doesn't show any data from the table once i put code in the date criteria boxes. Any idea why? It's as if the boolean or "&" is throwing it off. My filter works with the other boxes where its only looking for strings.
Hi Nick, Thanks for your comments. There is an article on the website withe formulas to copy. Sent me an email from the contact form and I will help. My best wishes Trev
Hi Trevor, I have created a file where the users select the filter criteria with the help of a drop down and when the macro is run the data from the master file is filtered, on the basis of the drop down and is saved in another sheet. Then I have applied a pivot on this filtered data and there are a number of sheets which gets updated. These activities happen when you run the macro. Now i have a huge dataset i need to handle and i have no option but to use a powerpivot, but i am not sure how i can use the same logic in a data model in excel 2013. Could you please help me out?
Excellent tutorial Trevor. Thank u! I did everything in the video for my data but mine stopped working after i filtered it a couple of times. What can I do?
Thank you Trevor. This is exactly what I was looking for. However, I'm having some issues with the VBA code and wonder if the CurrentRegion is not available in Excel 2010. When I get to the step of making the range dynamic, I do not get the drop menu of different codes like you do. Any suggestions?
Hi Mooonbug, CurrentRegion works in 2010. Make sur you have a blank row above it and a blank column either side of it. You need to check if the dynamic named range is working. Go to the Nmae Manager / Refers to: There are several tutorials on creating named ranges on the website to give you. For testing you could use a static named range. Best wishes trev
Hi Trevor! Great tutorial! Is this also applicable to all text/numbers data? (eg. individual's contact details) Since my data does not contain dates, what formula should be used in the criteria range? Thanks for the help!
Hi trevor, I am working on a similar project with advanced data validation. I have 4 filter boxes and 1st one is a dynamic name range, 2nd one is dependent on the first via offset(cascade, match...) function. Third is a dynamic & 4th is also a dynamic. When i recorded advanced data val for these 4 just as you explained in the video, only the 1st 2 filters work! The 3rd & 4th do not apply/execute at all.. any idea whats going on?
Been looking for this for a long time as this will help me a lot. Where I am now stuck is after filtering, I want to record another macro to copy the text that’s been filtered and paste onto another sheet. When I run the macro, it only copies and paste the amount of rows I selected when I recorded the macro. Any tips would be appreciated. That is for all your videos.
Hi Trevor, Thank you so much for the video. Is it possible that out of entire Raw data we can filter only required column. For example, Out of all Raw data I need Data only for Received data, Company, Amount and Type by applying AND filter for Type. I mean data for only four column by applying filter on anyone of them.
I'm wondering why you don't use Power Query to do this work? Power Query seems to do a lot of the bulk of the work that Advanced Filters use. Of course the reason I'm asking is because I'm having difficulties with them :) Love, love your videos, they're fantastic, great pace, clear voice, and nice background music 10 out of 10.
Absolutely great! Thank you very much! Do you sell any advanced Excel non-VBA book? Because as silly as it may sounds, I know all I need from VBA, but I would like to know thigs as the ones you have shown in this video, like the usage of the software without programming in VBA.
Every time I run the filter Macro the data will appear about 6 columns to the right and 2 rows up. It will clear just fine. Did I miss something in the formula that is making the filter show up in another location?
Hi! It was a great tutorial but I am facing some problem regarding filtering the data. It works perfectly when I enter values in criteria but when put formulas in it it does not work. Hope you could help me figure out that
Trevor, how can I amend this so every time I run the code it adds to the next empty row? Every month I intend to use this method to pull the latest months data using a month column as my criteria range but don't want the previous data to clear.
Hi Paul, I'm not exactly sure what you are referring to Paul. This tutorial shows how you can filter data in a database with certain criteria. I have attached the link below to a basic yet more comprehensive tutorial that shows how to filter data and also our data to a database. www.onlinepclearning.com/excel-phone-book-contact-manager-excel-2010/ please accept my best wishes trev
Hi Mr Trevor thank you for the excellent tutorial. However, I have been experiencing an issue with the FilterMe Macro, where after copying it into the interface, several rows of data goes missing. Hence there are missing data when I run the FilterMe Macro. Hope you can help me on this. Thanks!
Great tutorial...love all your tutorials....learn a lot from them.........I have applied this particular tutorial to one of my excel databases. Everything works except for 1 criteria. The last criteria I use is whether or not an item has been rejected. If it has then in the database under the column REJECT, an "X" is put in the record.When I start filtering, it filters like I want except when you put an 'x' in the reject criteria.It will give only the rejected items the first time and all the other times with other criteria. But when you take the 'x' out of the criteria box, it still filters for rejected items, instead of all other records that are not rejects.Right now there are over 12,000 records and more added every day. The column that has the "x" for reject - some records has an 'x' and some don't. I use the formula =if(f4="","",f4) in the criteria range. What could be the problem? I can send you the workbook if you want.
Hi Donald, X is text so if the criteria is blank you should put in the * to enclude all. I notice in your formula you have "" if blank. This may help to solve your problem. Trev
Hey, trev....I changed the formula like you suggested and when I filter now, it brings back no records at all. With or without any thing in the Reject criteria and even with another criteria added.
wanted you to know that I figured out why this wasn't working for me.The "Reject" criteria column had "x"s in the cells to indicate rejected loads. All other cells in the column were blank to indicate that the loads were accepted. I used "yes " and "no" instead and it started working. I modified the formula a bit. Now the spreadsheet works beautifully.
Hi Trevor, I followed everything you did which works fine. However, when changes was made to the code to call CurrentRegion, the code will not run. How can I fix this issue? Thank you
Hi Sir, I followed your examples as shown on the video and it works well except for the dates. Wondering where can the problem be? Without dates it works fine. Are ther more examples for using dates for advanced filters.
Great tutorial, thank you very much. There is one more issue would like to check with you: If my data include URL links, after filter, the links not working anymore. How can I keep the links after filtering?
***** Hi Ch, You can loop through the range and create links from the filtered data in the next column. Like this (Alter range to suit) Sub Macro2() For Each c In Range("K1:K1000") c.FormulaR1C1 = "=HYPERLINK(RC[-1])" Next c End Sub best wishers Trev
Trevor Easton Thank you very much for your information, I am trying but cannot make it work due to my limited knowledge of VB. Can you please have a look of my following files to check how can recreate the link in filtered result? www.dropbox.com/s/zhwelx8n9uodf76/Filter.rar?dl=0
Thanks for the great video. Is it also possible to make the itempool you can select in one Dropdownlist dependend on the item you have selected in the Dropdownlist before? Want to let Excel check e.g. okay you have choosen a date in the dropdownlist and there is only one category in the datapool (data sheet) in subject to this date. So Excel should only give me the possibilty to select in the Dropdownlist "Category" this specific category item. Best wishes M
awesome tutorial.. but i need your help.. my "interface" sheet need number of filtered data (B9 and down below). and below the filtered data i need a row for the place and date and name. thank u
Hi Trevor, I'm having the same problem as Philip and many others; when I insert the 'criteria range' formulas and leave the reference cells blank, the macro does not seem to recognize the criteria range as being blank hence not returning the full dataset. Any idea what's going wrong?
Hi Jeroen, Best to do the project as shown then change it to suit your needs. There is a lot more information about this feature at the link below. The complete tutorial is at the link below. www.onlinepclearning.com/vba-advanced-filter-multiple-criteria/ Best wishes Trev
Date range is not working. Please help. When I'm putting the formula based on what you have written the filter is not working. Im stucked with it since yesterday. Thanks
Hi. Nice job. I am truing to place Criteria Range to another sheet and I am facing difficulty. Filter process does not run. Is it possible or they must be to the same sheet as destination.
Hey Trevor, i am having the same issue as Nick. Have tried everything i can think of, just will not work when the if statements for the dates are entered. Have tried Named Ranges, no Named Ranges, your formulas to a "T" even tried them as an array formula as my range is in a table. I have no idea!!! Only the Data Validation on the text string or the date cells blank (with no formulas in them). Even when blank (as per the if statement), won't work. Any further thoughts?? I have so many applications for this if i can get the first one to work!! Dean.
hi Dean, Impossible to tell from the information. I have used advanced filters with variable criteria on some very large data sets and it has worked fine. I would suggest go back to basic. Run the filter manually (from the ribbon) with the different criteria sets. If it will not run from the ribbon then it will not work with VBA. good luck Trev
Hi, Trevor Easton... Good Job! I have question, my advance filter is working fine except on numbers. ie Car Year Model. Please tell me why filter can search text but not only number? Thank You.
Hi Syed, I am not sure if your car year are in separate columns. Do you have text and numbers in different columns or text and numbers as a string in the same column. Trev
Trevor, thanks for your great sharing. However, I have a question regarding the advanced filter and I would be really grateful if you could help me with that. I have set up my own codes for the Advanced filter and I did NOT set the codes for the clear button, Then I notice that if I do not clear the content before I use the Advanced filter button on the same sheet again, the only changing cells will be only on column A (my copy to is A4). at first I thought that it is the problem of my codes, but even if I manually go for the advanced filter and select the data range, criteria range etc, manually again, the only changing data is on column A and other data wont change. I then conduct a further experiment to clear the data before I tried to use the advanced filter (both my VBA button and the manual way), they work fine. May I know why? is there anyway to make the filter work without having to clear the content before i use it for the second time on the same sheet? Thanks in advance!
Trevor Easton Hi Trevor, thanks for your prompt reply! I notice that I need to run a line of code to clear the data. without that clear, the vba just does not work.... Thank! Thanks for posting this video as well! I would like to ask a further question. I am now handling about 20 kind of economic data (eg. GDP inflation rate, unemployment rate) for 250 countries. These massive data are all stored in a worksheet. So in this worksheet, there are around 5000 rows of data. May I know if there is any way that I can distribute the 20 economic data to 20 different worksheets? ie, one worksheet will only have GDP and the other only has inflation rate etc.... Thanks in advance! Have a good day!
Please help me. What codes should I use for login form. I have already my login form with username (textbox), password (textbox) and login (command button). This is the case, I want to proceed to another sheet of excel everytime I logging in to the form. Please link me to another video if it is possible. Thank you.
Thank you. Great video. Helps a lot. Could you please explain how extract specified columns instead of extracting/filtering all of them ? Thank you very much. Kind Regards, T.
I did the same thing but with different DATA. When I input "DATES" it won't filter anything but when I input a "CATEGORY" it works. Do you think is the formatting of the dates?
In the dynamic named ranges, what is the benefit of performing the counta on only the first 100 rows rather than doing "counta( $D:$D) - 1" (subtracting one to account for the header)?
hi You can use D:D if you start from row 1. In the tutorial I start from row 5 so you need to specify the range. D:D will give inaccurate results in this situation. Trev
+Trevor Easton I thought the point of counta was that it counts only "non-blank" cells, so as long as you subtract out any header rows you have at the beginning of D:D, the range will go down the correct number of rows. For example, your list in D:D is header + 11 records. If I put the formula "=counta(D:D)" in another cell just to get the count, I get a result of 12, even though your header is in row 4 and the data ends on row 15. since your offset already points to D5, that's always your starting point, just using counta(D:D) would give you the range D5:D16, and using counta(D:D)-1 for the header would give the expected range of D5:D15. At least, that's my understanding. Am I missing something? To bring it a step further, if D1:D3 were non-blank, I would just have to subtract the number of non-blank cells cells that I know are not part of my list. in the case of header data in each of the first 4 cells, use counta(D:D)-4. If my logic is wrong on this, I'd really appreciate your help understanding why! Your have me scared... if I'm wrong, I'll have a bunch of spreadsheets I need to go back and fix...
hi whatever your name, My comments are borne from hard experience. (D:D)-1 or -4 should work. (I have not tested) I specify the range when creating the dynamic named range and their can be no errors. I have had numerous requests to fix ranges caused by specifiying the whole column over many years. It may also be worth noting that if you refer to a dynamic named range in VBA and it blank then you get an error. A solution is to refer to the header as the starting point and offset a row. Hope this helps. Thanks for you feedback Best wishes Trev
Thank you Trevor, very helpful but I need to filter more than two values. For example, i want to filter 2 tax categories and want all require data. Is that possible!
HI Rahul, Yes you can filter with OR or AND. Run some tests manually. The criteria for AND is horizontal as in this tutorial. OR is vertical. eg HEADER Criteria 1 Criteria 2 etc This is the OR filter setup You can run AND and OR together Best wishes trev
+Trevor Easton Thank you so much for your prompt reply. Like me there may be several subscriber who might want to learn advance filter with more than one value. Is it possible to make a video on this topic? I appreciate your work, hence the request! :)
For some reason if I leave the date field blank the advanced filter will not work. I am using the following formula for the start and end date: Start =IF(C5="","",">="&C5); End =IF(D5="","","
thank you so much this tutorial was awesome however I would like to ask you a question. what if we had more than one item in the cells for the category item? for example if you had in one of the cells travel, education. I would appreciate it if you could reply to this. thanks
Kasra Rostamkhany Hi Kasra, If you add * wild card operator either side of the criteria with the formula it should pick up "everything containing" This should allow for 2 words or parts of 2 words. Best wishes Trev
Trevor Easton Thank you for your reply Trevor, I tried this all day and have gone through everything I can but I cannot get the result I want. what I have at the moment in the data sheet is that I have a column called Region in each cell there are several countries separated by a comma for example in each cell I have "UK,US, France and etc". I want to be able to search for France for example but get the result from the data sheet whether or not France is in the beginning, middle or at the end. The problem is that at the moment the filter finds only the cells where France is in the beginning and if France is not the first item in the cell like: "Brazil, France" then the filter would not reconcile that and would only return the result with cells where France is in the beginning of a cell like: "France,Brazil" I apologies for the long question but I would much appreciate it if you could help me as this is driving me crazy (: Many thanks Kasra
Kasra Rostamkhany Hi Kasra, You can use wildcards either side which will filter values containing. Eg *France* or *Fra* But the main problem here is your data structure. You should put the countries in separate columns or rows with unique column headers. Best wishes Trev
Hi Trevor, thank you for responding to my question you have been so kind to try to help me; however, I cannot change the data structure as there can be many countries in each cell. Hence having so many filters makes no sense. Just to explain the data, the rows are companies and the column is countries they work with. Many thanks Kasra
When I add the 'FilterMe' macro to the button it says 'the exact range has a missing or illegal field name' though I followed the tutorial exactly step by step. Whats going on?
Hi Mandy, Check that you have named the criteria headers exactly the same as in the data. Headers must be included and exactly the same in the Extract/ Criteria and Copy to sections for the advanced filter to work. Trev
Great video, but I having issues with the criteria formulas not populating the correct data. I have set up the IF functions to return blanks if nothing in the respective cell, but for some reason it is filtering my data with zeros. Example would be if I don't put in any formulas into my criteria search all my data appears. If I set it up with the formulas already in place and have not entered any data so they show as blanks the only data that appears is the data that has zeros in all the fields. Am I missing a format error or setting error in excel that is using my formula for a blank as a zero. Thanks,
Hi Jeff, I am not sure what the problem is. It could depend on the type of data used. Why would there be zeros in a data set? Generally blank is what is used if there are no values. If you are using zeros then the formula would be. A cell reference is cell N6 =IF(C6=0,"",">" &C6) Best wishes Trev
dear mr. Easton, Sir I have a problem with the soruce fields. In my source ı have cells that connected with formulas. such as : unit price*quantity=total price. When generating the filtering (as gathering data according to my selection criteries), I getting error, and proccess fails. how can ı solve this issue.... deleting that formulas in my source, fixes the problem. however ı need those formulas. Writing the formulas into the output fields doesnt much serve my aim. THANKS IN ADVANCE. Ati.
Thanks for the video. I noticed that its only possible to filter on the first part of the strings using this method. What if I would like to use any part of the string in the search? In your example it could be searching for 'ravel' instead of 'travel' in the tax category.
Hi Jesper, I is common for operators to be used to change the way data is filtered. The wild card* is often used for this. Search operators for advanced filters in google. Data that contains would be *text*. Trev
Works as a charm , thanks. I have an additional question a little off topic that you might be able to help with. In my this list I have multiple records with the same info except for the date. Would it be possible to include in the filter only to give me the record with the newest date?
Hi Trevor. I find this tutorial very helpful. However, can you help me how to make criteria with at least 5 rows. I tried to edit your code and adjust the criteria. However, it doesn't seem that its getting the right value that i want. Hope you can help
Hi Glicel, You can run criteria in rows. This will give you OR as the overator. For get the code at first. Work from the ribbon (Advanced Filter)and run it manually. When you have it working then record your code. Trev
How do I create a user form that accepts multi search criteria from the user and displays the search result on the user form without the user seeing the excel data
hi Rukadore, Current region will work on a dataset. There must be a blank row above the header and a blank column yo the left and right of the data. This isolates the dataset. Trev
Trevor, My good man, I'm 79 years old and don't like to code when I can pick and choose. Therefore, I really like your presentation as it is going to save me some wear and tear on my cogs. Keep up the good work. I'm getting myself MSO 2021 on DVD for Christmas. I'm a retired coder of many languages, but I'm amazed as what you can do with Excel and Open Office.
THANK YOU!!!!! After literally WEEKS of researching, online training and assistance from the experts at Mr Excel, this tutorial has FINALLY resolved my issue of creating a filter for my data. This has made it so easy to follow along, and really easy to understand. You are amazing, and have saved me SO MUCH TIME!!!!!
Thanks.
Trev
I remember watching this video a long time ago and lost it, and after some time wanted to get back to it and could not find it. Fortunately today it happened and it saved the day. Thank you for making this video and sharing the knowledge.
Dear Mr. Trevor , You are one of the greatest professionals in this world
Really too appreciated from your tutorials
This saved my VBA project from being a complete FAILURE! THANK YOU SOOOO MUCH!
This video not just provides example of an advanced filter, but also other concepts such as macros, assigning buttons to macros to filter and clear data, and functions such as Offset. Very informative.
Hi Trevor, This session was extremely useful. I was trying more complicated way of coding with combo box, drop down and was not so successful. Thanks a lot for keeping it simple yet so useful.
العفو منك لا اجيد الانجليزية بشقيها كتابتاً أو نطقاً ولكن استمتعت من طريقة شرحك على الفيديو وإظهار ما تقوم به من خلال تقريب الشاشة , لذا وجب عليا شكرك لنشر العلم وايصالة لمن لا يفهم الانجليزية بشكل بيسط
مع خالص تحياتي
من اليمن
This video helped me out so much! This is the key for making sharp and efficient dashboards. Thanks a lot!
I have to thank you from the bottom of my heart for this tutorial! I have searched high and low to look for what i needed and this perfectly explains precisely what I needed! If I could shake your hand in person I would!!!!! THANK YOU! :)
Just amazing, I love the trick u used to enable advanced filter to copy the data to another worksheet!
Hi Giang,
Thanks for the feedback. Appreciated...
Trev
Thank you for this video kind sir!!! Your teaching level is fantastic!!! God Bless You!
Thanks Trevor, your method is a lot easier than using VB, Thanks again
All i can say is, Thank you very so much, it helped me a lot!
Same
Ive been trying to do this for days. Thanks a lot for this tutorial.
Thank you very much. Well done. It goes to the point with very clear comments and examples
Dear Trevor - this tutorial was amazing, thank you!
Thank you for this video. It saves me a lot of time. I really appreciate your hard work. Take Love
Really helped me this video, by the way sir I want to see my last available row in filterd sheet for which I might want to scroll down,, is der any technique for that
Trevor,
Is there a limit to how many Criteria can be in the Criteria Block?
I'm finding that your example is limited to 4 criteria - I can't get the Advanced Filter to go beyond this!
Hi Philip,
There is no limit but bear in mind that the criteria are mutually inclusive. ( eg. 2 sets of numbers or dates need specific attention)
Sometimes because of that you may need to run a couple of separate filters.
Best wishes
Trev
Trevor Easton Thanks for your reply.I'm guessing that the Criteria Block that you've shown, with all criteria on the one row, is a series of AND formulas. However, if a new row of criteria is added to the block then we have a series of OR formulas? This additional set of criteria on an additional row works better for me.
Thanks trev you've been an angel in my thesisstory ! thanks a lot ! cheerz belgium
Trevor, Do you have plans for any other Advanced Filter Videos?
I've found this video to be very helpful. Thanks for your expertise.
Hey Trevor! Thank you so much.
Could you please tell me how to access to your website? Apparently, it doesn't work.
Excellent Tutorial.. This is very useful....Much appreciated..Thanks Trevor..
Hi Hari,
Glad you liked it.
Trev
Hi Trevor, Super helpful tutorial! I do have one question though. I am using this to filter a larger data set to display data only related to a specific rep (Sales Rep Name is my List and Criteria). I noticed, that if that field is left blank, and we click the Filter Data button, it pulls in everything. I want to prevent my users from doing that. Is there anyway to restrict this so that there must be something added to the criteria field, or a way to restrict it from pulling the entire data set?
This is fantastic!!. I mean all your videos about excel. Thank you its a great help.. God Bless.. Keep it UP^ Sir.
Awesome video as usual. I can't get the Dates to work. In the criteria boxes for the 2 dates, I am putting the code in exactly as yours, making changes to the cells that are in my spreadsheet, but for some reason, the filterme macro doesn't show any data from the table once i put code in the date criteria boxes. Any idea why? It's as if the boolean or "&" is throwing it off. My filter works with the other boxes where its only looking for strings.
Hi Nick,
Thanks for your comments.
There is an article on the website withe formulas to copy.
Sent me an email from the contact form and I will help.
My best wishes
Trev
You are a wonderful teacher. Thank you so much!
Thanks Trevor,
It's realy worth to watch your tutorials.
Great one.......
Expecting lot more from you...... :) :) :)
pavan kumar
Thanks
Much appreciated.
Trev
Hi! Thank you for sharing the video. Do you think it will work if I only want a subset of fields?
Very well explained... helped tremendously... Thnx for sharing
Hi Trevor,
I have created a file where the users select the filter criteria with the help of a drop down and when the macro is run the data from the master file is filtered, on the basis of the drop down and is saved in another sheet. Then I have applied a pivot on this filtered data and there are a number of sheets which gets updated. These activities happen when you run the macro. Now i have a huge dataset i need to handle and i have no option but to use a powerpivot, but i am not sure how i can use the same logic in a data model in excel 2013. Could you please help me out?
Excellent tutorial Trevor. Thank u! I did everything in the video for my data but mine stopped working after i filtered it a couple of times. What can I do?
Hi Trevor, Happy New Year!
A question, Does the source data need to be ordered by a certain criteria?
Thank you Trevor. This is exactly what I was looking for. However, I'm having some issues with the VBA code and wonder if the CurrentRegion is not available in Excel 2010. When I get to the step of making the range dynamic, I do not get the drop menu of different codes like you do. Any suggestions?
Hi Mooonbug,
CurrentRegion works in 2010. Make sur you have a blank row above it and a blank column either side of it.
You need to check if the dynamic named range is working. Go to the Nmae Manager / Refers to:
There are several tutorials on creating named ranges on the website to give you. For testing you could use a static named range.
Best wishes
trev
Hi Trevor! Great tutorial!
Is this also applicable to all text/numbers data? (eg. individual's contact details)
Since my data does not contain dates, what formula should be used in the criteria range?
Thanks for the help!
Hi trevor, I am working on a similar project with advanced data validation. I have 4 filter boxes and 1st one is a dynamic name range, 2nd one is dependent on the first via offset(cascade, match...) function. Third is a dynamic & 4th is also a dynamic. When i recorded advanced data val for these 4 just as you explained in the video, only the 1st 2 filters work! The 3rd & 4th do not apply/execute at all.. any idea whats going on?
Been looking for this for a long time as this will help me a lot. Where I am now stuck is after filtering, I want to record another macro to copy the text that’s been filtered and paste onto another sheet. When I run the macro, it only copies and paste the amount of rows I selected when I recorded the macro. Any tips would be appreciated. That is for all your videos.
Wow! This is one I was looking for
Hi Trevor, Thank you so much for the video.
Is it possible that out of entire Raw data we can filter only required column.
For example, Out of all Raw data I need Data only for Received data, Company, Amount and Type by applying AND filter for Type. I mean data for only four column by applying filter on anyone of them.
I'm wondering why you don't use Power Query to do this work? Power Query seems to do a lot of the bulk of the work that Advanced Filters use. Of course the reason I'm asking is because I'm having difficulties with them :)
Love, love your videos, they're fantastic, great pace, clear voice, and nice background music 10 out of 10.
Absolutely great! Thank you very much! Do you sell any advanced Excel non-VBA book? Because as silly as it may sounds, I know all I need from VBA, but I would like to know thigs as the ones you have shown in this video, like the usage of the software without programming in VBA.
Matias Franck
Hi Matias,
I do not have anything except what is on the website
www.onlinepclearning.com
Trev
Every time I run the filter Macro the data will appear about 6 columns to the right and 2 rows up. It will clear just fine. Did I miss something in the formula that is making the filter show up in another location?
Hi, thanks for your tutorial.
If i want exact match not only similar what should i do?
Hi! It was a great tutorial but I am facing some problem regarding filtering the data. It works perfectly when I enter values in criteria but when put formulas in it it does not work. Hope you could help me figure out that
Trevor, how can I amend this so every time I run the code it adds to the next empty row? Every month I intend to use this method to pull the latest months data using a month column as my criteria range but don't want the previous data to clear.
Hi Paul,
I'm not exactly sure what you are referring to Paul. This tutorial shows how you can filter data in a database with certain criteria. I have attached the link below to a basic yet more comprehensive tutorial that shows how to filter data and also our data to a database.
www.onlinepclearning.com/excel-phone-book-contact-manager-excel-2010/
please accept my best wishes
trev
Hi Mr Trevor thank you for the excellent tutorial. However, I have been experiencing an issue with the FilterMe Macro, where after copying it into the interface, several rows of data goes missing. Hence there are missing data when I run the FilterMe Macro. Hope you can help me on this. Thanks!
Great tutorial...love all your tutorials....learn a lot from them.........I have applied this particular tutorial to one of my excel databases. Everything works except for 1 criteria. The last criteria I use is whether or not an item has been rejected. If it has then in the database under the column REJECT, an "X" is put in the record.When I start filtering, it filters like I want except when you put an 'x' in the reject criteria.It will give only the rejected items the first time and all the other times with other criteria. But when you take the 'x' out of the criteria box, it still filters for rejected items, instead of all other records that are not rejects.Right now there are over 12,000 records and more added every day. The column that has the "x" for reject - some records has an 'x' and some don't. I use the formula =if(f4="","",f4) in the criteria range. What could be the problem? I can send you the workbook if you want.
Hi Donald,
X is text so if the criteria is blank you should put in the * to enclude all. I notice in your formula you have "" if blank.
This may help to solve your problem.
Trev
Hey, trev....I changed the formula like you suggested and when I filter now, it brings back no records at all. With or without any thing in the Reject criteria and even with another criteria added.
Hi Donald,
Can you show me the formula?
Trev
=if(F4="","*",F4)
wanted you to know that I figured out why this wasn't working for me.The "Reject" criteria column had "x"s in the cells to indicate rejected loads. All other cells in the column were blank to indicate that the loads were accepted. I used "yes " and "no" instead and it started working. I modified the formula a bit. Now the spreadsheet works beautifully.
Great one .. really helped me in my projects :)
Thanks Trevor
You're a Legend!
Hi Trevor, I followed everything you did which works fine. However, when changes was made to the code to call CurrentRegion, the code will not run. How can I fix this issue? Thank you
Hi Sir, I followed your examples as shown on the video and it works well except for the dates. Wondering where can the problem be? Without dates it works fine. Are ther more examples for using dates for advanced filters.
Hi Peter,
The complete tutorial is at the link below.
www.onlinepclearning.com/vba-advanced-filter-multiple-criteria/
Best wishes
Trev
Thanks so much, you are touching so much life's... Wanted to know if you could pick the data from multiple sheet that will be of great help
+Divine Awudi
No that is not possible
Either combine the data or run multiple advanced filter code.
Trev
Thanks 👍🏼
Great tutorial, thank you very much. There is one more issue would like to check with you: If my data include URL links, after filter, the links not working anymore. How can I keep the links after filtering?
*****
Hi Ch,
You can loop through the range and create links from the filtered data in the next column.
Like this (Alter range to suit)
Sub Macro2()
For Each c In Range("K1:K1000")
c.FormulaR1C1 = "=HYPERLINK(RC[-1])"
Next c
End Sub
best wishers
Trev
Trevor Easton
Thank you very much for your information, I am trying but cannot make it work due to my limited knowledge of VB. Can you please have a look of my following files to check how can recreate the link in filtered result? www.dropbox.com/s/zhwelx8n9uodf76/Filter.rar?dl=0
Thanks for the great video. Is it also possible to make the itempool you can select in one Dropdownlist dependend on the item you have selected in the Dropdownlist before? Want to let Excel check e.g. okay you have choosen a date in the dropdownlist and there is only one category in the datapool (data sheet) in subject to this date. So Excel should only give me the possibilty to select in the Dropdownlist "Category" this specific category item. Best wishes M
Hi Marcus,
This tutorial will show how to do this
www.onlinepclearning.com/excel-dependent-data-validation-cascading-data-validation/
Best wishes
Trev
Thank you so much. Can you tell me how I can do this with one criteria in multiple sheets?
awesome tutorial.. but i need your help.. my "interface" sheet need number of filtered data (B9 and down below). and below the filtered data i need a row for the place and date and name. thank u
Hi Trevor,
I'm having the same problem as Philip and many others; when I insert the 'criteria range' formulas and leave the reference cells blank, the macro does not seem to recognize the criteria range as being blank hence not returning the full dataset.
Any idea what's going wrong?
Hi Jeroen,
Best to do the project as shown then change it to suit your needs.
There is a lot more information about this feature at the link below.
The complete tutorial is at the link below.
www.onlinepclearning.com/vba-advanced-filter-multiple-criteria/
Best wishes
Trev
Thanks Trevor!
Date range is not working. Please help. When I'm putting the formula based on what you have written the filter is not working. Im stucked with it since yesterday. Thanks
Hi. Nice job.
I am truing to place Criteria Range to another sheet and I am facing difficulty. Filter process does not run. Is it possible or they must be to the same sheet as destination.
Hey Trevor, i am having the same issue as Nick. Have tried everything i can think of, just will not work when the if statements for the dates are entered. Have tried Named Ranges, no Named Ranges, your formulas to a "T" even tried them as an array formula as my range is in a table. I have no idea!!!
Only the Data Validation on the text string or the date cells blank (with no formulas in them). Even when blank (as per the if statement), won't work.
Any further thoughts??
I have so many applications for this if i can get the first one to work!!
Dean.
hi Dean,
Impossible to tell from the information. I have used advanced filters with variable criteria on some very large data sets and it has worked fine.
I would suggest go back to basic. Run the filter manually (from the ribbon) with the different criteria sets. If it will not run from the ribbon then it will not work with VBA.
good luck
Trev
Hi! I can not filter between two dates. Do I have to formate my sheet somehow to recognyse dates or what? Thank you. And by the way, you are awsem.
Brilliant tutorial, very handy for work :)
Hi, Trevor Easton... Good Job!
I have question, my advance filter is working fine except on numbers. ie Car Year Model.
Please tell me why filter can search text but not only number?
Thank You.
Hi Syed,
I am not sure if your car year are in separate columns.
Do you have text and numbers in different columns or text and numbers as a string in the same column.
Trev
Trevor, thanks for your great sharing.
However, I have a question regarding the advanced filter and I would be really grateful if you could help me with that.
I have set up my own codes for the Advanced filter and I did NOT set the codes for the clear button, Then I notice that if I do not clear the content before I use the Advanced filter button on the same sheet again, the only changing cells will be only on column A (my copy to is A4).
at first I thought that it is the problem of my codes, but even if I manually go for the advanced filter and select the data range, criteria range etc, manually again, the only changing data is on column A and other data wont change.
I then conduct a further experiment to clear the data before I tried to use the advanced filter (both my VBA button and the manual way), they work fine.
May I know why? is there anyway to make the filter work without having to clear the content before i use it for the second time on the same sheet?
Thanks in advance!
Hi So,
You many need to run a line of code to clear the data as you run the macro.
Best wishes
Trev
Trevor Easton Hi Trevor, thanks for your prompt reply! I notice that I need to run a line of code to clear the data. without that clear, the vba just does not work.... Thank!
Thanks for posting this video as well! I would like to ask a further question.
I am now handling about 20 kind of economic data (eg. GDP inflation rate, unemployment rate) for 250 countries.
These massive data are all stored in a worksheet. So in this worksheet, there are around 5000 rows of data. May I know if there is any way that I can distribute the 20 economic data to 20 different worksheets? ie, one worksheet will only have GDP and the other only has inflation rate etc....
Thanks in advance! Have a good day!
Thats just awesome, thank you very much for the wonderful information.
Thanks Sir, This tutorial really helped me.. thank you a lot.
Thanks Paresh for the positive feedback.
best wishes
Trev
Welcome...
Please help me. What codes should I use for login form. I have already my login form with username (textbox), password (textbox) and login (command button). This is the case, I want to proceed to another sheet of excel everytime I logging in to the form. Please link me to another video if it is possible. Thank you.
Thank you. Great video. Helps a lot.
Could you please explain how extract specified columns instead of extracting/filtering all of them ?
Thank you very much.
Kind Regards,
T.
Hi Tolga,
All you have to do is only add the columns you want in the Copyto Range. You can have specific columns only or all.
Best wishes
Trev
I did the same thing but with different DATA. When I input "DATES" it won't filter anything but when I input a "CATEGORY" it works. Do you think is the formatting of the dates?
+Gilberto Cano I have the same issue. Did you get any answer?
Very Helpful! Thanks a lot Trevor!
In the dynamic named ranges, what is the benefit of performing the counta on only the first 100 rows rather than doing "counta( $D:$D) - 1" (subtracting one to account for the header)?
hi
You can use D:D if you start from row 1. In the tutorial I start from row 5 so you need to specify the range.
D:D will give inaccurate results in this situation.
Trev
+Trevor Easton I thought the point of counta was that it counts only "non-blank" cells, so as long as you subtract out any header rows you have at the beginning of D:D, the range will go down the correct number of rows. For example, your list in D:D is header + 11 records. If I put the formula "=counta(D:D)" in another cell just to get the count, I get a result of 12, even though your header is in row 4 and the data ends on row 15. since your offset already points to D5, that's always your starting point, just using counta(D:D) would give you the range D5:D16, and using counta(D:D)-1 for the header would give the expected range of D5:D15. At least, that's my understanding. Am I missing something?
To bring it a step further, if D1:D3 were non-blank, I would just have to subtract the number of non-blank cells cells that I know are not part of my list. in the case of header data in each of the first 4 cells, use counta(D:D)-4.
If my logic is wrong on this, I'd really appreciate your help understanding why! Your have me scared... if I'm wrong, I'll have a bunch of spreadsheets I need to go back and fix...
hi whatever your name,
My comments are borne from hard experience.
(D:D)-1 or -4 should work. (I have not tested)
I specify the range when creating the dynamic named range and their can be no errors.
I have had numerous requests to fix ranges caused by specifiying the whole column over many years.
It may also be worth noting that if you refer to a dynamic named range in VBA and it blank then you get an error.
A solution is to refer to the header as the starting point and offset a row.
Hope this helps.
Thanks for you feedback
Best wishes
Trev
Trevor, great video. Thanks.
That was really Great tutorial
Sir small doubt
Can we retrieve this data to user form like this by drop down Search events.
If so please support.
Hey how I can search for a rang of data for the similarities from set of data. is there any cod for that?
Thank you Trevor, very helpful but I need to filter more than two values. For example, i want to filter 2 tax categories and want all require data. Is that possible!
HI Rahul,
Yes you can filter with OR or AND.
Run some tests manually.
The criteria for AND is horizontal as in this tutorial.
OR is vertical.
eg
HEADER
Criteria 1
Criteria 2
etc
This is the OR filter setup
You can run AND and OR together
Best wishes
trev
+Trevor Easton Thank you so much for your prompt reply. Like me there may be several subscriber who might want to learn advance filter with more than one value. Is it possible to make a video on this topic? I appreciate your work, hence the request! :)
For some reason if I leave the date field blank the advanced filter will not work. I am using the following formula for the start and end date: Start =IF(C5="","",">="&C5); End =IF(D5="","","
Thank you! You made my work easier. haha!
thank you so much this tutorial was awesome however I would like to ask you a question.
what if we had more than one item in the cells for the category item? for example if you had in one of the cells travel, education.
I would appreciate it if you could reply to this.
thanks
Kasra Rostamkhany
Hi Kasra,
If you add * wild card operator either side of the criteria with the formula it should pick up "everything containing"
This should allow for 2 words or parts of 2 words.
Best wishes
Trev
Trevor Easton
Thank you for your reply Trevor, I tried this all day and have gone through everything I can but I cannot get the result I want.
what I have at the moment in the data sheet is that I have a column called Region in each cell there are several countries separated by a comma for example in each cell I have "UK,US, France and etc". I want to be able to search for France for example but get the result from the data sheet whether or not France is in the beginning, middle or at the end.
The problem is that at the moment the filter finds only the cells where France is in the beginning and if France is not the first item in the cell like: "Brazil, France" then the filter would not reconcile that and would only return the result with cells where France is in the beginning of a cell like: "France,Brazil"
I apologies for the long question but I would much appreciate it if you could help me as this is driving me crazy (:
Many thanks
Kasra
Kasra Rostamkhany
Hi Kasra,
You can use wildcards either side which will filter values containing.
Eg *France* or *Fra*
But the main problem here is your data structure. You should put the countries in separate columns or rows with unique column headers.
Best wishes
Trev
Hi Trevor,
thank you for responding to my question you have been so kind to try to help me; however, I cannot change the data structure as there can be many countries in each cell. Hence having so many filters makes no sense.
Just to explain the data, the rows are companies and the column is countries they work with.
Many thanks
Kasra
Excellent video, Thanks ...Thanks ...Thanks .
When I add the 'FilterMe' macro to the button it says 'the exact range has a missing or illegal field name' though I followed the tutorial exactly step by step. Whats going on?
Hi Mandy,
Check that you have named the criteria headers exactly the same as in the data. Headers must be included and exactly the same in the Extract/ Criteria and Copy to sections for the advanced filter to work.
Trev
Hi, how to filter excel table just by entering a part of number in the heder(cell value), please code here
Great video, but I having issues with the criteria formulas not populating the correct data. I have set up the IF functions to return blanks if nothing in the respective cell, but for some reason it is filtering my data with zeros. Example would be if I don't put in any formulas into my criteria search all my data appears. If I set it up with the formulas already in place and have not entered any data so they show as blanks the only data that appears is the data that has zeros in all the fields. Am I missing a format error or setting error in excel that is using my formula for a blank as a zero.
Thanks,
Hi Jeff,
I am not sure what the problem is. It could depend on the type of data used. Why would there be zeros in a data set? Generally blank is what is used if there are no values. If you are using zeros then the formula would be.
A cell reference is cell N6
=IF(C6=0,"",">" &C6)
Best wishes
Trev
Just to add a little point. Zeros are values and will be filtered depending on the criteria.
dear mr. Easton,
Sir I have a problem with the soruce fields. In my source ı have cells that connected with formulas. such as : unit price*quantity=total price.
When generating the filtering (as gathering data according to my selection criteries), I getting error, and proccess fails. how can ı solve this issue.... deleting that formulas in my source, fixes the problem. however ı need those formulas.
Writing the formulas into the output fields doesnt much serve my aim. THANKS IN ADVANCE. Ati.
Great Explanation Thanks a lot !
Thanks for the video. I noticed that its only possible to filter on the first part of the strings using this method. What if I would like to use any part of the string in the search? In your example it could be searching for 'ravel' instead of 'travel' in the tax category.
Hi Jesper,
I is common for operators to be used to change the way data is filtered. The wild card* is often used for this. Search operators for advanced filters in google.
Data that contains would be *text*.
Trev
Wild card either side so the text
Works as a charm , thanks. I have an additional question a little off topic that you might be able to help with. In my this list I have multiple records with the same info except for the date. Would it be possible to include in the filter only to give me the record with the newest date?
Jesper Glar Nielsen H Jesper,
Use the Max function in a formula to show the latest date and use this as the criteria. Add it with some VBA.
Trev
Hi Trevor. I find this tutorial very helpful. However, can you help me how to make criteria with at least 5 rows. I tried to edit your code and adjust the criteria. However, it doesn't seem that its getting the right value that i want. Hope you can help
Hi Glicel,
You can run criteria in rows. This will give you OR as the overator.
For get the code at first. Work from the ribbon (Advanced Filter)and run it manually.
When you have it working then record your code.
Trev
Trevor Easton Hi Trevor! Thank you for your help. But what I did was I added a new button and add the code and adjust the criteria. Thanks a lot!
Good job !!!, Thanks for sharing knowledge.
Appreciated
Trev
How do i get it to filter part of a number like if i know the last 4 digits but not the first 3
what happens if one of your criteria is a blank cell?
Superb sir thank u so much
How do I create a user form that accepts multi search criteria from the user and displays the search result on the user form without the user seeing the excel data
Excellent! many many thanks!
You are awesome man ! :)
For some reason when I add the CurrentRegion to my macro it stops working. It says "The extract range has a missing of invalid field name."
hi Rukadore,
Current region will work on a dataset. There must be a blank row above the header and a blank column yo the left and right of the data. This isolates the dataset.
Trev
+Trevor Easton Ok. That explains it. I will adjust the data set. Awesome videos by the way.
Can u make the dropdown list dependable?
I love this.. Thank you
Happy to help
Trev
Why isn't my dropdown box linked to my filter?
thx, looks great !