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
Problem.xlsx
Nik YugoSrbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
You can achieve that with Advanced Filter. I will do it on the file and post shortly
0
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:

="<="&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
0
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nik YugoSrbAuthor Commented:
I did it with advance filtering, but there was some tricky part about "active sheet"
0
Rob HensonFinance AnalystCommented:
Yes, when starting the wizard the cursor has to be on the sheet where you want the results.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.