• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Data using Advanced Filters in Excel VBA


Hi !

It's almost a step by step article ‼

You can also do it manually using the Macro recorder …
​
 
Hi Maneesh

Have to agree with Mark a step by step article is quite a bit of work for someone. Your old friend Google might point you in the right direction. From memory Deb Dalglesh has some excellent articles on the Advanced filter.

I just posted some code similar to your request here;

http://chandoo.org/forum/threads/macro-to-copy-paste-based-on-column-date.14269/

There is no detailed explanation but you should be able to follow along.

You could add the data validation and adjust the range in the filter to make something which might be acceptable.

Take care

Smallman
 
Maneesh

You will learn more by downloading the model from the post and working through the post step by step pulling the model apart in the process
Look at the named Formula, understand what each does
Look at an execute the code and run it line by line using F8
then come back here with questions when you get stuck
 
Hi Maneesh ,

Let us start with the first fact about Advanced Filters :

If you make the sheet RawData the active sheet , and then click on Advanced Filter , and select the option Copy to another location , you can only copy to locations on the Active Sheet ; if you select a location on another tab , Excel will display an error message.

So if you wish to have filtered data copied to a different sheet , ensure that you make that sheet the active sheet before the statement to run the Advanced Filter.

The second fact about Advanced Filter is that Excel will only work with criteria cells which are laid out horizontally ; the reason for this is that the raw data is organized as a table with several columns ; Excel always checks the criteria using the first row of the criteria range as the column headers , and the data in the second and following rows of the criteria range as the actual criteria. This naturally means that the first row in the criteria range should have labels which are identical to the column headers of the data table.

For example , if the column in the raw data table is labelled Product , if your criteria range has a column header Products , Excel will give an error message.

In the blog post , the filtered data is on a separate sheet named Filter ; the criteria are user selectable using drop-downs which are pre-defined static drop-downs ; they have not been derived from the raw data using formulae. In your case , you need to do this manually , by going through your data , and listing out the unique values so that they can be used in data validation drop-downs. In the blog post , all the DV drop-down lists are to be found in the tab named Master.

Since the criteria selections have been laid out vertically , in order to be used as a criteria range compatible with the Advanced Filter , the same data has been copied to the RawData tab using formulae.

Other than this , there is just one VBA statement which does the filtering and copying.

If you need any more explanations , please ask.

Narayan
 
Thanks fo
NARAYANK991 said:
Hi Maneesh ,

Let us start with the first fact about Advanced Filters :

If you make the sheet RawData the active sheet , and then click on Advanced Filter , and select the option Copy to another location , you can only copy to locations on the Active Sheet ; if you select a location on another tab , Excel will display an error message.

So if you wish to have filtered data copied to a different sheet , ensure that you make that sheet the active sheet before the statement to run the Advanced Filter.

The second fact about Advanced Filter is that Excel will only work with criteria cells which are laid out horizontally ; the reason for this is that the raw data is organized as a table with several columns ; Excel always checks the criteria using the first row of the criteria range as the column headers , and the data in the second and following rows of the criteria range as the actual criteria. This naturally means that the first row in the criteria range should have labels which are identical to the column headers of the data table.

For example , if the column in the raw data table is labelled Product , if your criteria range has a column header Products , Excel will give an error message.

In the blog post , the filtered data is on a separate sheet named Filter ; the criteria are user selectable using drop-downs which are pre-defined static drop-downs ; they have not been derived from the raw data using formulae. In your case , you need to do this manually , by going through your data , and listing out the unique values so that they can be used in data validation drop-downs. In the blog post , all the DV drop-down lists are to be found in the tab named Master.

Since the criteria selections have been paid out vertically , in order to be used as a criteria range compatible with the Advanced Filter , the same data has been copied to the RawData tab using formulae.

Other than this , there is just one VBA statement which does the filtering and copying.

If you need any more explanations , please ask.

Narayan
Click to expand...
Thanks for this excellent explanation !! I'll give it a try... if I'm stuck somewhere, I'll be back.
 
NARAYANK991 said:
Hi Maneesh ,

Let us start with the first fact about Advanced Filters :

If you make the sheet RawData the active sheet , and then click on Advanced Filter , and select the option Copy to another location , you can only copy to locations on the Active Sheet ; if you select a location on another tab , Excel will display an error message.

So if you wish to have filtered data copied to a different sheet , ensure that you make that sheet the active sheet before the statement to run the Advanced Filter.

The second fact about Advanced Filter is that Excel will only work with criteria cells which are laid out horizontally ; the reason for this is that the raw data is organized as a table with several columns ; Excel always checks the criteria using the first row of the criteria range as the column headers , and the data in the second and following rows of the criteria range as the actual criteria. This naturally means that the first row in the criteria range should have labels which are identical to the column headers of the data table.

For example , if the column in the raw data table is labelled Product , if your criteria range has a column header Products , Excel will give an error message.

In the blog post , the filtered data is on a separate sheet named Filter ; the criteria are user selectable using drop-downs which are pre-defined static drop-downs ; they have not been derived from the raw data using formulae. In your case , you need to do this manually , by going through your data , and listing out the unique values so that they can be used in data validation drop-downs. In the blog post , all the DV drop-down lists are to be found in the tab named Master.

Since the criteria selections have been laid out vertically , in order to be used as a criteria range compatible with the Advanced Filter , the same data has been copied to the RawData tab using formulae.

Other than this , there is just one VBA statement which does the filtering and copying.

If you need any more explanations , please ask.

Narayan
Click to expand...
One piece of information I need is whether my raw data (where all the data exists) needs to be converted to a table first in order to do this. If I first have to convert my raw data into a table, then this will create problems with the application or may cause my application to begin giving errors. Is this so ? If yes, then I first need to put a button on my Data sheet with a macro, clicking which will copy my dynamic named range data to a different sheet in the same workbook, name this sheet as "RawData" and then I can implement data extraction using Advanced Filters with Data Validation List. Somewhere in the code I saw that the raw data needed to be converted into a table, hence this doubt. Please help me clarify this.
Thank you.
 
Maneesh

You didn't do your research by opening the file in the thread I pointed you to. You only read part of the above. You said you wanted to learn but you didn't read what people posted for you.

Smallman
 
Hi Maneesh ,

If you think about it , a table is just a feature in later versions of Excel , which has certain conveniences , in adding data , specifying formulae ,...

There is absolutely no reason why the Advanced Filter will not work with data which has not been setup as a table.

If you see the wording in the Advanced Filter dialog box , it specifically mentions the word List ; this means that the Advanced Filter will work with even a single column of data.

Try it and see.

Narayan
 
Smallman said:
Maneesh

You didn't do your research by opening the file in the thread I pointed you to. You only read part of the above. You said you wanted to learn but you didn't read what people posted for you.

Smallman
Click to expand...
Hi Smallman,
I take every response very positively and do not count it as negative. Nobody wants to bog down the person who is trying to learn. Hence, I have not regarded your comments in a negative manner. After having read your comments, I did go to the page you pointed out and tried to understand it. Then I came back to my own application and tried to understand and do exactly what was shown in the Chandoo Blog post. I am still testing the code and will post if I need further clarifications. That's one of the great things about you guys, you always try to lift up the person who is trying to learn and not push him/her down. Thanks a lot !
 
NARAYANK991 said:
Hi Maneesh ,

If you think about it , a table is just a feature in later versions of Excel , which has certain conveniences , in adding data , specifying formulae ,...

There is absolutely no reason why the Advanced Filter will not work with data which has not been setup as a table.

If you see the wording in the Advanced Filter dialog box , it specifically mentions the word List ; this means that the Advanced Filter will work with even a single column of data.

Try it and see.

Narayan
Click to expand...
Hi Narayan!
Thank you for further trying to explain and clarify the process. I've learnt some good things through your post. Will shortly let you know if I need more help! Thanks once again!
 
Hi Maneesh

Thanks for the thinly veiled innuendo. Non the less your comments from the post above suggest you did not do what you said. If you had done what you said (first sentence post 7) you would have known that the spreadsheet on the link was not in a table at all. So I am sorry I do not have a great deal of time for you coming back in here and suggesting you took the time to understand as you would have seen straight away - "I don't need a table to do this".

Happy to help but you have to be honest when you comment.

Smallman
 
Smallman said:
Hi Maneesh

Thanks for the thinly veiled innuendo. Non the less your comments from the post above suggest you did not do what you said. If you had done what you said (first sentence post 7) you would have known that the spreadsheet on the link was not in a table at all. So I am sorry I do not have a great deal of time for you coming back in here and suggesting you took the time to understand as you would have seen straight away - "I don't need a table to do this".

Happy to help but you have to be honest when you comment.

Smallman
Click to expand...
Hi Smallman,
Thanks for saying that too! Yes, the spreadsheet on the link you said is not a table and I already saw that, thank you. The examples given are just samples of what an application can do or behave like, and we are to change or alter them to suit our own needs. I have already understood its not in a table. I think I'm not wrong in saying what I just said, "changing them to suit our own needs." and seeing if that works for me. Everyone's application is different. Regarding you not having time and offering suggestions, well buddy, that's entirely up to you, is'nt it ? Whether you like it or not, whether you suggest something or not, in the end, I'm still gonna make the application !
 
The words Cease and Desist sound appropriate here for the 3 deleted posts

The words in the New Users Please Read refer to using Love and Respect in posts.
http://chandoo.org/forum/threads/new-users-please-read.294/
Posters who fail to comply risk losing posting access or even total access

The Chandoo.org Forums will not tolerate breaches of these basic rules

Hui...
 
Last edited:
You must log in or register to reply here.
Back
Top