Forum Discussion
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?
Please find attached an example.
formula in cell A13: =FILTER($A$2:$E$8;B2:B8="e")
13 Replies
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.
- Many thanks, I've saved this formula, now it's time to wait till MS will release the new functions.
- 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 helpsuse 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","")
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
