Datameer X provides a feature to filter data on a worksheet by specified criteria. Additional filters can be added as well as regular expressions or formulas can be used. |
When filtering data from a workbook sheet, you can choose between:
The options available to filter vary based on the type of data in the column selected. A filtered worksheet is based on sample data defined from the source of the data. When first creating a filtered worksheet, the displayed data is based on the current data sampling. After the workbook has been run, the joined sheet calculations are applied to the full data. |
When creating a filtered sheet that displays no data, Datameer X informs you that the empty sheet is displayed on the current sample data. Running the workbook again creates a new sampling for the sheets that take into account the calculation of the filtered sheet. |
To filter data:
Advanced filter formulas are similar to those used in a workbook's formula bar though without column name completion and less error handling. You can create a formula that combines multiple conditions using multiple referenced columns or use nests functions and constants. Text-based expressions must return a Boolean value and are required to reference columns in the current sheet. When the expression that is applied to the current record returns 'false', the record is dropped, otherwise it remains. |
Datameer X recommends developing and testing your advanced filter formulas in a separate copied worksheet before applying to your actual data. |
To filter data with the advanced filter:
To update a filter from an already filtered sheet:
Find the needed filter expressions within the list. |
Data Type | Expression Name | Expression Description |
---|---|---|
String/ Number/ Date/ List | equals | The value entered exactly matches a value in the column specified. |
String/ Number/ Date/ List | does not equal | The value entered does not exactly match a value in the column specified. |
String/ Number/ Date/ Boolean/ List | contains | The value entered is present in the column specified. |
String/ Number/ Date/ Boolean/ List | does not contain | The value entered is not present in the column specified. |
String | ends with | The value entered match starting with the last characters in the specified column. |
String | not ends with | The value entered does not match starting with the last characters in the specified column. |
String | starts with | The value entered match starting with the first characters in the specified column. |
String | does not begin with | The value entered does not match starting with the first characters in the specified column. |
String | matches regexp | The regular expression value matches a regular expression in the specified column. |
Any | blank | Displays the data where the value of the specified column is empty. |
String | not blank | Displays the data where the value of the specified column is not an empty string. |
String/ Number | contained in | The value entered with quotation marks " " includes the entire data from a cell in a specified column. |
String/ Number | not contained in | The value entered with quotation marks " " does not include the entire data from a cell in a specified column. |
String/ Number/ Date/ List/ Boolean | is empty cell | Displays the data where the value of the specified column is <null>. |
String/ Number/ Date/ List/ Boolean | cell not empty | Displays the data where the value of the specified column is not <null>. |
Number | less | Displays numbers less than the number entered. |
Number | less equals | Displays numbers less than or equal to the number entered. |
Number | greater | Displays numbers greater than the number entered. |
Number | greater equals | Displays numbers greater than or equal to the number entered. |
Date | after | Displays dates for the specified column after the date entered. |
Date | after equals | Displays dates for the specified column after or equal to the date entered. |
Date | before | Displays dates for the specified column before the date entered. |
Date | before equals | Displays dates for the specified column before or equal to the date entered. |
List | is empty list | Displays lists that have no elements from a specified column. |
List | not an empty list | Displays lists that contain elements from a specified column. |
Boolean | true | Displays boolean values that are true from a specified column. |
Boolean | false | Displays boolean values that are false from a specified column. |
CONTAINS(#a;"500") || ISBLANK(#a) || !CONTAINS(#a;"800") |
This example can be read like this:
"Filter all entries that are contained in the column named 'a' with the value '500' OR filter entries where the value of the column 'a' is empty OR filter entries from column 'a' which do not contain the value '800'."