May 20 2022 07:01 AM
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")
May 20 2022 07:26 AM
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})
)
May 20 2022 07:34 AM
May 20 2022 09:07 AM
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
May 20 2022 09:14 AM
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
May 20 2022 09:29 AM - edited May 20 2022 09:35 AM
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","")
May 20 2022 09:30 AM
Solution
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
May 20 2022 09:37 AM
May 20 2022 10:14 AM
May 20 2022 10:15 AM
May 20 2022 12:24 PM
May 20 2022 01:57 PM
May 20 2022 03:00 PM
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.
May 22 2022 11:35 PM
May 20 2022 09:30 AM
Solution
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