Forum Discussion
Write a custom filter
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 dupl...
Thanks very much.
Some further questions on this:
The list range just refers to the cells that the filter will apply to. Does it have to be in one column? Or could it be a continuous range over multiple columns? Or could it even be separate, distinct columns (not connected)? Does the filter assume that if it finds a cell matching the criteria, that ROW will be hidden? Could you not filter columns instead?
Why is the default interface of the filter menu to enter a criteria range? Is it because you are expected to pass, say, a comma-separated list of values, and if any of those values occur in the filter range, it counts as a match? It seems inelegant that if you want to pass a formula, you can, but you have to pass it via a cell reference. Why not skip that step and just include a field for entering a formula directly? I find it odd. Do you think other spreadsheet applications (such as LibreOffice) might have a different design in this regard?
The formula you wrote only mentions if a duplicate of a single value is found. Could we make it more general by putting a formula into every cell adjacent to a value which says, “count how many times the value TO MY LEFT occurs in some range”? Then we could filter that column for values greater than 1.
Thank you very much.
Some further questions on this:
The list range just refers to the cells that the filter will apply to. Does it have to be in one column? Or could it be a continuous range over multiple columns? Or could it even be separate, distinct columns (not connected)? Does the filter assume that if it finds a cell matching the criteria, that ROW will be hidden? Could you not filter columns instead?
Why is the default interface of the filter menu to enter a criteria range? Is it because you are expected to pass, say, a comma-separated list of values, and if any of those values occur in the filter range, it counts as a match? It seems inelegant that if you want to pass a formula, you can, but you have to pass it via a cell reference. Why not skip that step and just include a field for entering a formula directly? I find it odd. Do you think other spreadsheet applications (such as LibreOffice) might have a different design in this regard?
The formula you wrote only mentions if a duplicate of a single value is found. Could we make it more general by putting a formula into every cell adjacent to a value which says, “count how many times the value TO MY LEFT occurs in some range”? Then we could filter that column for values greater than 1.
Thank you very much.
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.