If you’ve stuck with this, you probably haven’t seen much new in the way of Advanced Filters.
Your Excel life is about to change.
In our example, we are filtering records (rows); making a large data set smaller from a row point of view.
When we point to a location to place the results, we get every field (column) from the original data.
But what if we don’t want all the fields (columns) from the original data set? And what if we want the output fields to be in a different order from the original data set?
This is where the trick comes in to play.
We can create an output of fields that don’t even contain the filter fields used for defining criteria.
- In the destination location for the results, create a mini header row that mimics the header names from the fields you want to include in the output.
- Start the Advanced Filter tool (Data -> Sort & Filter -> Advanced).
- Select “Copy to another location”.
- Set the List Range to the area holding the data (same as before: Data!$A$2:$K$117)
- Set the Criteria Range to the area holding the filter criteria (same as before: Data!$M$2:$N$3)
Here’s the most important step!
- In the Copy To field, select the headings you created in Step 1.
Advanced Filter returns only the records (rows) that match the criteria (same as before), but now filters the columns to only retain those fields of interest.
Notice that our results don’t include the Date or Article Description information used in the query.
We could even have Advanced Filter rearrange the column order by creating the output header in a different order.