Excel Filter function

[RogerL75]

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?

Please find attached an example.

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

[#2]

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})
)

[#3]

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

[#4]

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

[#5]

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

[#6]

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","")

[#7 · Accepted solution]

@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

[#8]

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.

[#9]

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

[#10]

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

[#11]

@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})
)

[#12]

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

[#13]

@RogerL75 

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

= CHOOSECOLS(
     FILTER(Table1, Table1[head2]="e"),
  5,3,4)

= FILTER(
     CHOOSECOLS(Table1,5,3,4),
  Table1[head2]="e")

with the row and column operations in either order.

[#14]

Many thanks, I've saved this formula, now it's time to wait till MS will release the new functions.