Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 27, 2021

Hogstad_Raadgivning 

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

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Feb 27, 2021

Thank you for your patience SergeiBaklan 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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 27, 2021

    Hogstad_Raadgivning 

    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

     

    • Hogstad_Raadgivning's avatar
      Hogstad_Raadgivning
      Iron Contributor
      Feb 27, 2021

      Hi, SergeiBaklan 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

    • Hogstad_Raadgivning's avatar
      Hogstad_Raadgivning
      Iron Contributor
      Feb 27, 2021

      Thank you again SergeiBaklan 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

       

       

       

Resources