Advanced filter and some magic

For the vast majority of Excel users, when the word “data filtering” comes up in their head, only the usual classic filter from the tab Data – Filter (Data — Filter):

Such a filter is a familiar thing, no doubt, and for most cases it will do. However, there are situations when you need to filter by a large number of complex conditions in several columns at once. The usual filter here is not very convenient and I want something more powerful. Such a tool could be advanced filter, especially with a little “finishing with a file” (according to tradition).

Basis

To get started, insert a few empty lines above your data table and copy the table header there – this will be a range with conditions (highlighted in yellow for clarity):

There must be at least one empty line between the yellow cells and the original table.

It is in the yellow cells that you need to enter the criteria (conditions), according to which the filtering will then be performed. For example, if you need to select bananas in the Moscow “Auchan” in the III quarter, then the conditions will look like this:

To filter, select any cell in the range with the source data, open the tab Data And click the Additionally (Data — Advanced). In the window that opens, a range with data should already be automatically entered and we will only have to specify the range of conditions, i.e. A1:I2:

Please note that the range of conditions cannot be allocated “with a margin”, i.e. you can not select extra empty yellow lines, because an empty cell in the range of conditions is perceived by Excel as the absence of a criterion, and a whole empty line as a request to display all the data indiscriminately.

Switch Copy the result to another location will allow you to filter the list not right there on this sheet (as with a regular filter), but to unload the selected rows into another range, which then will need to be specified in the field Put result in range. In this case, we do not use this function, we leave Filter list in place and click OK. The selected rows will be displayed on the sheet:

Adding a Macro

“Well, where is the convenience here?” you ask and you will be right. Not only do you need to enter conditions into the yellow cells with your hands, but also open a dialog box, enter ranges there, press OK. Sad, I agree! But “everything changes when they come ©” – macros!

Working with an advanced filter can be greatly accelerated and simplified using a simple macro that will automatically run the advanced filter when conditions are entered, i.e. changing any yellow cell. Right-click on the tab of the current sheet and select the command Source text (Source Code). In the window that opens, copy and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)      If Not Intersect(Target, Range("A2:I5")) Is Nothing Then          On Error Resume Next          ActiveSheet.ShowAllData          Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion      End If  End Sub  

This procedure will automatically run when any cell on the current worksheet is changed. If the address of the changed cell falls into the yellow range (A2:I5), then this macro removes all filters (if any) and reapplies the extended filter to the source data table starting with A7, i.e. everything will be filtered instantly, immediately after entering the next condition:

So everything is much better, right? 🙂

Implementing complex queries

Now that everything is being filtered on the fly, we can go a little deeper into the nuances and disassemble the mechanisms of more complex queries in the advanced filter. In addition to entering exact matches, you can use various wildcard characters (* and ?) and mathematical inequality signs in a range of conditions to implement an approximate search. The character case does not matter. For clarity, I have summarized all the possible options in a table:

Subtle points:

• The * sign means any number of any characters, and ? – any one character.
• The logic in processing text and numeric queries is slightly different. So, for example, a condition cell with the number 5 does not mean to search for all numbers starting with five, but a condition cell with the letter B is equal to B*, i.e. will look for any text starting with the letter B.
• If the text query does not begin with the = sign, then you can mentally put * at the end.
• Dates must be entered in the US format month-day-year and through a fraction (even if you have Excel and regional settings).

Logical connectives AND-OR

Conditions written in different cells, but in the same line, are considered to be interconnected by a logical operator И (AND):

Those. filter bananas for me in the third quarter, precisely in Moscow and at the same time from Auchan.

If you need to link conditions with a logical operator OR (OR), then they just need to be entered in different lines. For example, if we need to find all orders of manager Volina for Moscow peaches and all orders for onions in the third quarter in Samara, then this can be specified in a range of conditions as follows:

If you need to impose two or more conditions on one column, then you can simply duplicate the column header in the criteria range and enter the second, third, etc. under it. terms. So, for example, you can select all transactions from March to May:

In general, after “finishing with a file”, an advanced filter comes out to be quite a decent tool, in some places no worse than a classic autofilter.

• Superfilter on macros
• What are macros, where and how to insert macro code in Visual Basic
• Smart tables in Microsoft Excel