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

Nik YugoSrb
Nik YugoSrb used Ask the Experts™
on
How to extract a list of values filtered by criteria "=today()+20"

In atach is .xlsx with my problem

Thank you in advance
Problem.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
You can achieve that with Advanced Filter. I will do it on the file and post shortly
Rob HensonFinance Analyst

Commented:
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:

="<="&TODAY()+20

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?
Filter-Problem.xlsx

Author

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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Finance Analyst
Commented:
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.

Author

Commented:
I did it with advance filtering, but there was some tricky part about "active sheet"
Rob HensonFinance Analyst

Commented:
Yes, when starting the wizard the cursor has to be on the sheet where you want the results.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial