Forum Discussion

AurelienSimon's avatar
AurelienSimon
Copper Contributor
Feb 06, 2024

Using filter in dynamic array function

Hi everybody,

I often use dynamic arrays functions in my Excel reports, especially FILTER ans GROUPBY, with filters.

To do this easier to use, I use drop-down lists, based on UNIQUE functions.

User can change the filter with these drop-down lists.

The problem is I have to select a value in these lists, I can't leave the cell blank.

Is there a method to say to the filter: hey, take all the values, not just one?

Thanks for helping me and my apologies about my approximate English.

Have a nice day.

Aurelien

BI & Data Analysis
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Bronze Contributor
    Feb 06, 2024

    AurelienSimon A couple of options you might want to try include:

     

    =FILTER(array, (array1=IF(cell1="", array1, cell1))*(array2=IF(cell2="", array2, cell2)))

     

    -OR-

     

    =LET(
    all, SEQUENCE(ROWS(array))^0,
    FILTER(array, IF(cell1="", all, array1=cell1)*IF(cell2="", all, array2=cell2))
)

     

    Please see the attached workbook for examples...

    • AurelienSimon's avatar
      AurelienSimon
      Copper Contributor
      Feb 06, 2024
      Hi djclements,
      Thanks a lot for responding.
      I'm not familiar with the LET function but I have to learn it in the coming days.
      I'm going to take a look at both of these two formulas in order to understand them properly.
      I'll tell you if all is OK.
      Have a nice day, thank you very much.
      Aurélien

Resources