Forum Discussion
Write a custom filter
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:
- 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.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.
- Thanks very much.
What about passing any VBA function as the criteria - a named function I have written in my VBA editor - instead of this sort of Excel-style function? Is that possible with the VBA AdvancedFilter function or should I just write my own filter manually by using the Row.Hidden = True property?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.
