 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 filter function and get back only some special columns from the data table and in a different order?

formula in cell A13: =FILTER(\$A\$2:\$E\$8;B2:B8="e") 13 Replies

# Re: Excel Filter function

The following should do what you want:

``````=LET(
f; FILTER(A2:E8; B2:B8="e");
INDEX(f; SEQUENCE(ROWS(f)); {5\3\4})
)``````

# Re: Excel Filter function

You can return columns in different order using INDEX(... , .... , {5,3,4})
so = INDEX(FILTER(Datatable, Datatable[head2]="e",""), , {5,3,4})
you might need to define all the rows
so = INDEX(FILTER(Datatable, Datatable[head2]="e",""), SEQUENCE(ROWS(Datatable)), {5,3,4})
and you could reverse it by first defining the columns inside the FILTER:
so = FILTER( INDEX(Datatable, , {5,3,4}), Datatable[head2]="e","")
and again you might have to define all the rows
hope that helps

# Re: Excel Filter function

Hi@L z.

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 # Re: Excel Filter function

Hi@mtarler ,

similar to the result that I get with the formula that L.Z. has posted, I get the following result with your formula.

Do you know what I did wrong?

Roger # Re: Excel Filter function

use comma instead of semi-colon in the list (I believe this is the main issue in both cases above)
=INDEX( .....; {1,8,3})
and since you will need to use the SEQUENCE(ROWS(Datatable)) to call all the rows for those defined columns I will edit my previous answer to suggest:

= FILTER( INDEX(Datatable, SEQUENCE(ROWS(Datatable)), {5,3,4}) , Datatable[head2]="e","")

best response confirmed by RogerL75 (Occasional Contributor)
Solution

# Re: Excel Filter function

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 # Re: Excel Filter function

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.

# Re: Excel Filter function

A very confusing topic. My formula delimiter is the ; and for an array of columns I must use the \, i.e. {1\7\3}

# Re: Excel Filter function

ah yes, confusing indeed. You had the best idea by sending the file and let Excel sort it all out

# Re: Excel Filter function

@L z., @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})
)``````

# Re: Excel Filter function

Glad this worked Roger & Thanks for providing the DE version, hopefully this will help...

# Re: Excel Filter function

Since you are using 365, you will soon have a further option available to you, namely CHOOSECOLS.

``````= CHOOSECOLS(