Write a custom filter

%3CLINGO-SUB%20id%3D%22lingo-sub-2774430%22%20slang%3D%22en-US%22%3EWrite%20a%20custom%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774430%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20write%20a%20custom%20filter%2C%20either%20in%20VBA%20or%20just%20normal%20Excel%20filtering%20functions%2C%20rather%20than%20using%20the%20pre-existing%20ones%20in%20Excel%3F%20For%20example%2C%20in%20Excel%20you%20can%20hide%20%2F%20filter%20out%20duplicate%20entries%20in%20a%20column%20or%20highlight%20duplicate%20entries%2C%20but%20I%20would%20like%20to%20write%20a%20filter%20function%20which%20filters%20out%20(not%20highlight)%20unique%20entries%20and%20leaves%20duplicate%20cells%20visible.%20Is%20it%20possible%20for%20me%20to%20write%20such%20a%20custom%20function%3F%20How%20so%3F%20It%20seems%20like%20filters%20generally%20take%20a%20%22criteria%20range%22%20as%20their%20input%3B%20the%20instructions%20they%20use%20to%20filter.%20This%20in%20turn%20is%20made%20of%20a%20column%20header%20plus%20a%20formula%20below%2C%20but%20it%20seems%20the%20formula%20is%20restricted%20to%20simple%20text%20or%20number%20matching%20or%20greater%20than%20%2F%20less%20than%20operations.%20Could%20I%20instead%20enter%20into%20the%20formula%20my%20own%20VBA%20boolean%20function%2C%20perhaps%2C%20to%20be%20called%20on%20the%20value%20of%20every%20cell%20in%20the%20range%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2774430%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2774546%22%20slang%3D%22en-US%22%3ERe%3A%20Write%20a%20custom%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774546%22%20slang%3D%22en-US%22%3EDepends%20on%20what%20your%20requirement%20is%2C%20are%20you%20filtering%20out%20duplicate%20entries%20in%20a%20single%20column%20or%20in%20a%20combination%20of%20columns%3F%3CBR%20%2F%3EIf%20a%20single%20(and%20lets%20assume%20the%20data%20is%20in%20cells%20A2%3AA100)%2C%20you%20can%20add%20a%20column%20and%20use%20a%20formula%20like%20this%20to%20count%20the%20%23%20of%20times%20an%20item%20occurs%3A%20%3DCOUNTIF(%24A%242%3A%24A%24100%2CA2)%3CBR%20%2F%3EThen%20you%20simply%20filter%20your%20table%20for%20that%20new%20column%20on%20the%20value%201%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2774614%22%20slang%3D%22en-US%22%3ERe%3A%20Write%20a%20custom%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154117%22%20target%3D%22_blank%22%3E%40jukhamil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20create%20complex%20criteria%20by%20leaving%20the%20top%20cell%20of%20the%20criteria%20range%20(that%20ordinarily%20contains%20the%20field%20name%2Fcolumn%20header)%20blank%2C%20and%20entering%20a%20formula%20in%20the%20cell%20below%20it%20that%20returns%20TRUE%20for%20the%20values%20that%20you%20want%20to%20remain%20visible%20and%20FALSE%20for%20the%20others.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0775.png%22%20style%3D%22width%3A%20391px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312086i09BCA11FC13DBD1D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0775.png%22%20alt%3D%22S0775.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20E2%20is%20%3DCOUNTIF(%24E%245%3A%24E%2421%2C%24E5)%26gt%3B1.%20This%20returns%20TRUE%20if%20the%20first%20data%20cell%20E2%20is%20a%20duplicate.%3C%2FP%3E%0A%3CP%3EResult%3A%20only%20duplicate%20values%20are%20shown.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0776.png%22%20style%3D%22width%3A%20203px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312088i8F0833CB3C7E36CB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0776.png%22%20alt%3D%22S0776.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20see%20each%20value%20that%20has%20duplicates%20only%20once%2C%20tick%20the%20check%20box%20'Unique%20records%20only'.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20change%20the%20formula%20in%20E2%20to%20%3DCOUNTIF(%24E%245%3A%24E%2421%2C%24E5)%3D1%20and%20reapply%20the%20filter%2C%20only%20unique%20values%20remain%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0777.png%22%20style%3D%22width%3A%20201px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312089i8A189903E5FCA513%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0777.png%22%20alt%3D%22S0777.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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?

7 Replies
Depends on what your requirement is, are you filtering out duplicate entries in a single column or in a combination of columns?
If a single (and lets assume the data is in cells A2:A100), you can add a column and use a formula like this to count the # of times an item occurs: =COUNTIF($A$2:$A$100,A2)
Then you simply filter your table for that new column on the value 1

@jukhamil 

 

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.

S0775.png

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.

S0776.png

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:

S0777.png

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?

@jukhamil 

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.

Thank you. So this will basically add an additional column which shows if a specific value - the value in cell A2 - occurs in any other cell in that column. That would certainly be a good start, but I would have to then run it for every call value in that column, to catch all the duplicate. Is there some more generalizable way? For example, what if each cell made a relative reference to the cell one space to its left?
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.

@jukhamil 

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.