Feb 27 2021 03:52 AM
Hi.
The goal her is to filter the top texts from a table, when two criteria is met. I manage to get it when I us the whole top text range, but if i onlye select some of the top texts
=FILTER(Tabell1[#Topptekster];FILTER(Tabell1;Tabell1[Dato]=K2)="hallo";"") - OK
=FILTER(Tabell1[[#Topptekster];[Ansatt 1]:[Ansatt 3]];FILTER(Tabell1;Tabell1[Dato]=K2)="hallo";"") - #VALUE
=FILTER(INDEKS(Tabell1[#Topptekster];;2):INDEKS(Tabell1[#Topptekster];;4);FILTER(Tabell1;Tabell1[Dato]=K2)="hallo";"") #Value
Best regards
- Geir
Feb 27 2021 04:04 AM
@Geir Hogstad , what do you mean under top texts, table headers?
If so, you manually take some header names; define date for the row and would like to return from the table above columns filtered by date; or that's something different?
Feb 27 2021 04:15 AM
Not sure what is required, making the guess that could be
=FILTER(
INDEX(Tabell1[#All],
SEQUENCE(ROWS(Tabell1[#All])),
XMATCH(F13#,Tabell1[#Headers])),
COUNTIF(K2,Tabell1[[#All],[Dato]])
)
Feb 27 2021 04:23 AM
Thank you @Sergei Baklan Not excatly what I am trying to get.
I Would like to the 3 first (or what I decide using INDEX for the heade row.
Ansatt 1 - Ansatt 2 - Ansatt 3
If the date match, and the value in the Ansatt column match Hallo.
This result INDEX(Tabell1[#Topptekster];;2):INDEX(Tabell1[#Topptekster];;4) if the date match, and if it contains "Hallo""
I hope that explain it better.
Best Regards
- Geir
Feb 27 2021 04:35 AM
Thank you, but still not clear, sorry. You have predefined table headers, like
Ansatt 1 | Ansatt 2 | Ansatt 3 |
and from the you would like to select headers name for which in row with given date we have text "Hallo", i.e. returned result shall be
Ansatt 1 | Ansatt 2 |
, correct ?
But if we have few rows with specified date (01-Feb), shall we take into account columns where at least one "Hallo" for this date, or all rows shall be with "Hallo" ?
Feb 27 2021 04:59 AM
Thank you for your patience @Sergei Baklan It will only be one feb. 1, one feb. 2 Every date occur once.
If I select to show columns from D to F (Ansatt 1 to Ansatt 3) Index 2, index 4. and include "Hallo" The result should be:
01.feb Ansatt 1 - Ansatt 2
02.feb
03.feb Ansatt 2
04.feb
05.feb
06.feb Ansatt 2 - Ansatt 3
Best Regards
- Geir
Feb 27 2021 05:46 AM
If for one day only and pre-defined column names, formula could be
=LET(
selectedHeaders, $F$13#,
selectedDate, K2,
headers, Tabell1[[#Headers],[Ansatt 1]:[Ansatt 5]],
data, Tabell1[[Ansatt 1]:[Ansatt 5]],
condition, COUNTIF(selectedHeaders,headers) *
(XLOOKUP(selectedDate,Tabell1[Dato],data)="Hallo" ),
IFERROR(FILTER(headers, condition),"")
)
which returns
Feb 27 2021 06:06 AM
Thank you again @Sergei Baklan The table could expand. I might just leave it to this.
=FILTER(Tabell1[#HEADERS];FILTRER(Tabell1;Tabell1[Dato]=K2)="hallo";"") since it work.
Still confused why this not work:
FILTER(INDEX(Tabell1[#HEADERS];;2):INDEX(Tabell1[#HEADERS];;4);FILTER(Tabell1;Tabell1[Dato]=K2)="hallo";"")
Since =INDEX(Tabell1[#Headers];;2):INDEX(Tabell1[#HEADERS];;4) returns the list I want, but not if the criteria is met with filter ;)
Why does this work =FILTER(Tabell1[#HEADERS];FILTRER(Tabell1;Tabell1[Dato]=K2)="hallo";"")
And this returns #VALUE: =FILTER(INDEX(Tabell1[#HEADERS];;2):INDEX(Tabell1[#HEADERS];;4);FILTER(Tabell1;Tabell1[Dato]=K2)="hallo";"")
- Geir
Feb 27 2021 06:41 AM
Hi, @Sergei Baklan I Soleved. Had to use INDEX in the second filter as well
=FILTER(INDEX(Tabell1[#Headers];;2):INDEKS(Tabell1[#Headers];;4);FILTER(INDEX(Tabell1;;2):INDEX(Tabell1;;4);Tabell1[Dato]=K2)="hallo";"")
Thank you using your time and support Sergei.
Best Regards
- Geir
Feb 27 2021 09:31 AM
@Geir Hogstad , great to know you solved this, thank you for the update