Filter selection of top text

Steel Contributor

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

 

filter selction top texts.PNG

 

Best regards

- Geir

 

9 Replies

@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?

@Geir Hogstad 

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

 

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

@Geir Hogstad 

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" ?

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

@Geir Hogstad 

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

image.png

 

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

 

 

 

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

@Geir Hogstad , great to know you solved this, thank you for the update