Using FILTER function on Excel - View all Data ?

[trprasad78]

Hi Expert,

I would like to know can we show all data using FILTER FUNCTION.

I have 2 dorp down based on selection data showing already.

My question here is if I add "ALL" on each drop down based on ALL... filter has to show all data.

I though of using IF condition to check the drop down if the text is ALL then it has to show all data,
instead any possibility FILTER function itself have any option to show all data based on drop down to show all data.

any suggestions please.

 

[ETAF]

i'm sure there are better ways to do this - and other members i'm sure will reply

I could not see a way to get FILTER() to show all values , sure there is with a wild card or something

but what about using sort () for all
=IF(D2="ALL",SORT(A1:B5,1),FILTER(A1:B5,A1:A5=D2))
just an idea off the top of my head

 

[ETAF]

better way

use the dropdown list - and then filter based on the dropdown list
so
=IF(D2="ALL",FILTER(A1:B11,COUNTIF(O2:O5,A1:A11)),FILTER(A1:B5,A1:A5=D2))
where O2:O5 - is the dropdown list - datavalidation

 

[trprasad78]

better way

use the dropdown list - and then filter based on the dropdown list
so
=IF(D2="ALL",FILTER(A1:B11,COUNTIF(O2:O5,A1:A11)),FILTER(A1:B5,A1:A5=D2))
where O2:O5 - is the dropdown list - datavalidation

thank you so much for immediate reply.

Actually I would like to know only with filter function we able to view all data or NOT ? IF its their then how?

 

[Peter Bartholomew]

If the criterion evaluates to TRUE the entire list is returned. An error trap is useful if all may evaluate to FALSE.

= FILTER(list, criterion, "No matches")

 

[trprasad78]

Hi I have added another drop down, as I mentioned on above I have 2 drop down. when ever we change based on that it has to pull out the data.

is that possible to get the results only by FILTER FUNCTION

 

[Peter Bartholomew]

If I have understood your requirement correctly, it is possible.
As a single array formula

= FILTER(
    data,
    ((letters=letterFilter)+(letterFilter="All"))
      *((fruit=fruitFilter)+(fruitFilter="All"))
  )

or built up row by row, which allows the use of AND & OR

= LET(
    combinedCriterion, BYROW(data,
      LAMBDA(each,
        LET(
          letterCriterion, OR((each letter)=letterFilter, letterFilter="All"),
          fruitsCriterion, OR((each fruit)=fruitFilter,   fruitFilter="All"),
          AND(letterCriterion, fruitsCriterion)
        )
      )
    ),
    FILTER(data, combinedCriterion)
  )

As you see, I care nothing for brevity!

 

[trprasad78]

FILE IS WORKING GREAT!!! LET ME UNDER STAND THE FORMULA

THANK YOU SO MUCH