Sep 22 2021 07:45 AM
Sep 22 2021 07:45 AM
Is it possible to write a custom filter, either in VBA or just normal Excel filtering functions, rather than using the pre-existing ones in Excel? For example, in Excel you can hide / filter out duplicate entries in a column or highlight duplicate entries, but I would like to write a filter function which filters out (not highlight) unique entries and leaves duplicate cells visible. Is it possible for me to write such a custom function? How so? It seems like filters generally take a "criteria range" as their input; the instructions they use to filter. This in turn is made of a column header plus a formula below, but it seems the formula is restricted to simple text or number matching or greater than / less than operations. Could I instead enter into the formula my own VBA boolean function, perhaps, to be called on the value of every cell in the range?
Sep 22 2021 08:05 AM
Sep 22 2021 08:17 AM
You can create complex criteria by leaving the top cell of the criteria range (that ordinarily contains the field name/column header) blank, and entering a formula in the cell below it that returns TRUE for the values that you want to remain visible and FALSE for the others.
The formula in E2 is =COUNTIF($E$5:$E$21,$E5)>1. This returns TRUE if the first data cell E2 is a duplicate.
Result: only duplicate values are shown.
If you want to see each value that has duplicates only once, tick the check box 'Unique records only'.
If I change the formula in E2 to =COUNTIF($E$5:$E$21,$E5)=1 and reapply the filter, only unique values remain:
Sep 23 2021 06:48 AM
Sep 23 2021 08:09 AM
You can use a VBA function in the formula in cell E2 in my example, as long as it returns TRUE or FALSE for the first cell of the source range. You can use the criteria range in the WorksheetFunction.AdvancedFilter function too.
If that doesn't suit your needs, you can create your own filter code of course, but it will probably be slower.
Sep 27 2021 01:06 AM
Sep 27 2021 01:33 AM
Sep 27 2021 01:53 AM
Advanced filter can be applied to any rectangular range with field names (headers) in its first row.
It does not work with non-contiguous ranges, and it can only filter rows, not columns.
Another way to filter is AutoFilter. Click in one of the cells of the range, or select the entire range, then on the home tab of the ribbon, select Sort & Filter > Filter.
This will add filter arrows to the cells in the top row of the range. If you click one of the filter arrows, a drop-down menu with various filter options will be displayed.
I don't use LibreOffice, but I doubt that filtering will work differently there.