Forum Discussion

RogerL75's avatar
RogerL75
Copper Contributor
May 20, 2022
Solved

Excel Filter function

Hi there, I'd like to use the filter function in a special way.   The default result is that the filter function returns the entire row in the same order.   Is there a possibility to use the fi...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    May 20, 2022

    RogerL75 

     

    I suspect this is due to different Regional Settings... Download the attached file, Excel will figure out what the right/correct delimiter is for the columns in INDEX

Lorenzo's avatar
Lorenzo
Silver Contributor
May 20, 2022

Hi RogerL75 

 

The following should do what you want:

=LET(
   f; FILTER(A2:E8; B2:B8="e");
   INDEX(f; SEQUENCE(ROWS(f)); {5\3\4})
)
  • RogerL75's avatar
    RogerL75
    Copper Contributor
    May 20, 2022

    HiLorenzo

    something doesn't work and I don't know where I did the mistake.

    Don't be irritated that the following table looks like a bit different as the first one.

    If I use your formula I get the attached result.
    Due to the different language settings I had to change some symbols in the formula.

    Do you know what I did wrong?


    Roger

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 20, 2022

      RogerL75 

       

      I suspect this is due to different Regional Settings... Download the attached file, Excel will figure out what the right/correct delimiter is for the columns in INDEX

      • RogerL75's avatar
        RogerL75
        Copper Contributor
        May 20, 2022

        Lorenzo, mtarler 

        Many thanks for your support. The uploaded file has shown me the correct delimiter.

         

        This formula works fine 🙂

        =LET(
   f; FILTER(A2:H8; B2:B8="e");
   INDEX(f; SEQUENZ(ZEILEN(f)); {1.3.8.5.7})
)



Resources