Forum Discussion
How to filter visible cells by formula after applying filter to a dataset.
I have a dataset from A1:B11 (in real case many more). I apply a filter on dataset, for example filter data for Retailer1 and Retailer3. I want on a separate sheet to filter these visible cells using formula (not VBA). Preferably using a generic LAMBDA function like =FILTERVISIBLE(A2:B11). Any thought or idea to achieve it?
Filter visible cells only.
Or with exactly same idea
= LAMBDA(range, LET( IsVisible, LAMBDA(v, AGGREGATE(3,5,v)), FILTER( range, TAKE( MAP(range, IsVisible ),,1) ) ) )
6 Replies
Or with exactly same idea
= LAMBDA(range, LET( IsVisible, LAMBDA(v, AGGREGATE(3,5,v)), FILTER( range, TAKE( MAP(range, IsVisible ),,1) ) ) )Yes, AGGREGATE() has capability to handle errors and hidden cells. Thank for another response. Simpler version of formula is
=LET(arr,A2:B11,FILTER(arr,MAP(TAKE(arr,,1),LAMBDA(x,AGGREGATE(3,5,x)))))
Alternatively, without OFFSET:
=LAMBDA(tbl, LET( arr, tbl, viz, BYROW( INDEX(arr, , 1), LAMBDA(r, SUBTOTAL(103, r)) ), flt, FILTER(arr, viz), flt ) )Fantastic! This seems better than earlier.
I would add a helper column that check if the row is visible or not and then a regular FILTER formula as demonstrated in the picture below. In your real model, move the FILTER formula away from the data table as it will otherwise be partially hidden when you apply a filter to the data table.
Riny_van_Eekelen​ SUBTOTAL() is important here. After few research, I am able to make it as a generalize LAMBDA() formula. Here is lambda.
FILTERVISIBLECELLS=LAMBDA(Arr,LET(CheckVisible,SUBTOTAL(103,OFFSET(Arr,ROW(Arr)-ROW(TAKE(Arr,1,1)),0,1)),FILTER(Arr,CheckVisible)))And use of the function.
Screenshot of formula=FILTERVISIBLECELLS(A2:B11)
