I searched all over the internet for this and I didn't find a good tutorial so I decided to write one myself.
The goal is to filter some entries from a table (based on a criteria) and to generate a result table that doesn't contain all the columns from the source.
So here's the table. I hope the data is intuitive.
So the goal is to select the name and the city of the users that we've done business with in 2007. First we need to filter just the dates between 01.01.2007 and 01.01.2008. Second we must copy the results of the filter to another location and instruct Excel to copy just the name and city columns.
First we add 2 more columns to the right. These have the header name Date to reflect the column on which we want to filter. In the row below we write the rules. This is classic filtering methods.
Now start filtering. Select the Advanced Filter from the menu. In the pop-up select Copy to Another Location and fill in the following :
List Range : This is the input data. So we make this our table A1 to E12.
Criteria range : This is the date rules filter. Fill these in with the 2 columns to the right and their 2 rows
Copy to : Fill in the place where you want the data to be copied to. In our case it's A16 to B16.
Unique records only : Checked
Now hit ok and that should be it.
The results should look like this :
The most important product for any product manager
11 months ago