How to extract a List of Values Filtered by Criteria in excel

How to extract a list of values filtered by criteria "=today()+20"

In atach is .xlsx with my problem

Thank you in advance
Nik YugoSrbAsked:
Who is Participating?
Rob HensonFinance AnalystCommented:
Yes that's what Advanced Filter can do. Specify criteria for extract from main list (How it is sheet) and generate a second list that meet those criteria (How it should be sheet).  I used Advanced filter to create second list on same sheet just to demo but it can be on second sheet as per my description.

As mentioned, slight foible of Advanced Filter, the cursor has to be on second sheet when starting wizard which means you then have to select the data in the wizard as well as the criteria and the destination.
Rob HensonFinance AnalystCommented:
You can achieve that with Advanced Filter. I will do it on the file and post shortly
Rob HensonFinance AnalystCommented:
You actually have a couple of options, you can use AutoFilter or Advanced Filter on the list.

Auto Filter
Select the list of entries and from the Data tab click the big Filter/Funnel icon. This will place dropdown arrows beside each header. From the Last Date header dropdown select:

Date Filters > Before > Enter 21/11/17  click OK.

Downside of this is that you need to know what date you are filtering on, today + 20 days is relatively easy to calculate but other scenarios may not be.

Advanced Filter

Advanced Filter does the calculation for you by specifying a criteria and you can specify which columns to copy into extracted list.

The Advanced Filter requires 3 pieces of information:

1) Data list - to be selected
2) Criteria - see below
3) Destination (optional as can be filtered in place)

Criteria - the criteria is listed in a secondary table with the same headings as some (or all) of the columns of the source data. In this instance we are just using "Last Date". The criteria table is laid out as a header (the same as the source column) and then criteria listed beneath it. In this instance there is only one criteria and it will be a formula:


This will show as <43059  because 43059 is the serial number for today + 20

3) Destination - if you want to extract the data to a separate list you will need to specify where. You can also specify which columns to extract by using the same column headers as the source data. They can also be in a different order if required. If this is to be on a separate sheet, you will need to be on that sheet when you start the wizard.

To start the wizard, select the list of entries and from the Data tab select the Advanced button (smaller button next to big funnel for Auto Filter). The wizard will show and you will see the requirement for the 3 pieces of information as mentioned above. The "Copy to" (Destination) option will be greyed out until you tick the option for "Copy to another location". Use the range browser icons at the end of each selection to select your data (if not already selected), your criteria table and your destination. Click OK and the required data will be copied or the data will be filtered in place if you did not choose the Copy To option and set a location.

See attached file. One query with what you were expecting as a result. Why is Name15 included in the list when it is beyond 20 days from today?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Nik YugoSrbAuthor Commented:
Thx, but I tried something similar. I need something different, my plan is that, data in "how it is" sheet use for input, in sheet "how it's should be" generate table with specific condition and use that table in main report table.
Nik YugoSrbAuthor Commented:
I did it with advance filtering, but there was some tricky part about "active sheet"
Rob HensonFinance AnalystCommented:
Yes, when starting the wizard the cursor has to be on the sheet where you want the results.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.