Forum Discussion

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
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
FILTER_Roger.xlsx21 KB

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

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
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
FILTER_Roger.xlsx21 KB
- 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}) )
  - Lorenzo
    Silver Contributor
    May 20, 2022
    Glad this worked Roger & Thanks for providing the DE version, hopefully this will help...
- mtarler
  Silver Contributor
  May 20, 2022
  I believe you are sort of correct on the Regional Settings. It appears they replaced all the "," with ";" because of the regional difference EXCEPT within the bracket set {4, 3, 1} those "," vs ";" mean something different and shouldn't be replaced.
  - Lorenzo
    Silver Contributor
    May 20, 2022
    A very confusing topic. My formula delimiter is the ; and for an array of columns I must use the \, i.e. {1\7\3}