How to use Range.AdvancedFilter in the xlwings API way

The AdvancedFilter method of the Range object in Excel is a powerful tool for performing complex filtering operations, such as extracting unique records or filtering based on criteria ranges. In xlwings, this method can be accessed through the api property, which provides direct access to Excel’s underlying object model. This allows for sophisticated data manipulation directly from Python, leveraging Excel’s built-in capabilities for handling large datasets with custom criteria.

Functionality:
The AdvancedFilter method is primarily used to filter a range of data in place or to copy filtered results to another location. It supports two main actions: filtering in place (similar to using Excel’s Advanced Filter dialog without copying) and copying unique records to a specified destination. It can also use criteria ranges to define complex filtering conditions, such as multiple criteria across columns. This is useful for tasks like deduplication, conditional data extraction, and applying multi-condition filters that go beyond standard AutoFilter capabilities.

Syntax in xlwings:
In xlwings, the method is called via the api property on a Range object. The basic syntax is:

range_object.api.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

• Action: Specifies whether to filter the list in place or copy the results. It is a constant from the XlFilterAction enumeration:
• xlFilterInPlace (value 1): Filters the range without moving it.
• xlFilterCopy (value 2): Copies filtered results to another location.
• CriteriaRange: A Range object representing the criteria range. This is optional; if omitted, no criteria are applied. The criteria range should include column headers and conditions (e.g., using operators like “>”, “<“, or specific values). It can be set to None if not used.
• CopyToRange: A Range object specifying where to copy the results if Action is xlFilterCopy. This is required only for copy actions; otherwise, it can be omitted or set to None. It should include the destination’s top-left cell or a range with column headers.
• Unique: A boolean value (True or False) that indicates whether to extract only unique records. If True, duplicate rows are removed from the output. Default is False.

Example Usage:
Suppose we have an Excel workbook with data in the range A1:C10, where column A is “Name”, B is “Sales”, and C is “Region”. We want to filter for sales greater than 1000 in the “East” region and copy unique records to a new location. First, we define a criteria range in E1:F2 (with headers “Sales” and “Region” and conditions “>1000” and “East”). Here’s the xlwings code:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define the data range and criteria range
data_range = sheet.range('A1:C10')
criteria_range = sheet.range('E1:F2')
copy_dest = sheet.range('H1') # Starting cell for copied results

# Apply Advanced Filter to copy unique records based on criteria
data_range.api.AdvancedFilter(
Action=2, # xlFilterCopy
CriteriaRange=criteria_range,
CopyToRange=copy_dest,
Unique=True
)