Forum Discussion

rtayerst's avatar
rtayerst
Copper Contributor
Nov 04, 2024

Filter results into 2 columns

Hi all,

 

I think this should be relatively straight forward, but can't find a simple solution...My data has 2 columns, a week of the year and a 'score'

 

I need to create a spill result (so I can generate a dynamic chart), which separates the result of a filter into 3 columns:

 

Column1: Week 

Column2: Scores >50

Column 3: Scores <50

 

getting the three separate columns is not an issue, but it's combing them into a spill result that I can't fathom - using HSTACK with three FILTE functions means the scores/weeks do not align.

 

Any help much appreciated

  • rtayerst 

    For example:

     

    =LET(weeks, UNIQUE(A2:A10), HSTACK(weeks, BYROW(weeks, LAMBDA(cell, FILTER(C2:C10, (A2:A10=cell)*(B2:B10=2)*(C2:C10<=50), ""))), BYROW(weeks, LAMBDA(cell, FILTER(C2:C10, (A2:A10=cell)*(B2:B10=2)*(C2:C10>50), "")))))

     

    or formatted differently

    =LET(
      weeks, UNIQUE(A2:A10),
      HSTACK(
        weeks,
        BYROW(
          weeks,
          LAMBDA(cell,
            FILTER(
              C2:C10, 
              (A2:A10=cell)*(B2:B10=2)*(C2:C10<=50), 
              ""
            )
          )
        ),
        BYROW(
          weeks,
          LAMBDA(cell,
            FILTER(
              C2:C10, 
              (A2:A10=cell)*(B2:B10=2)*(C2:C10>50), 
              ""
            )
          )
        )
      )
    )
    • rtayerst's avatar
      rtayerst
      Copper Contributor

      HansVogelaar 

       

      apologies, I should have been clearer, yes for each week, there a multiple scores, but I only need the results where the 'multiplier' field = 2

       

      WeekMultiplierScore

      1

      153
      1268
      1130
      2283
      2160
      2149
      31

      53

      3180
      3227

       

      therefore the result would be

       

      Week<50>50
      1 68
      2 83
      327 

       

      • rtayerst 

        For example:

         

        =LET(weeks, UNIQUE(A2:A10), HSTACK(weeks, BYROW(weeks, LAMBDA(cell, FILTER(C2:C10, (A2:A10=cell)*(B2:B10=2)*(C2:C10<=50), ""))), BYROW(weeks, LAMBDA(cell, FILTER(C2:C10, (A2:A10=cell)*(B2:B10=2)*(C2:C10>50), "")))))

         

        or formatted differently

        =LET(
          weeks, UNIQUE(A2:A10),
          HSTACK(
            weeks,
            BYROW(
              weeks,
              LAMBDA(cell,
                FILTER(
                  C2:C10, 
                  (A2:A10=cell)*(B2:B10=2)*(C2:C10<=50), 
                  ""
                )
              )
            ),
            BYROW(
              weeks,
              LAMBDA(cell,
                FILTER(
                  C2:C10, 
                  (A2:A10=cell)*(B2:B10=2)*(C2:C10>50), 
                  ""
                )
              )
            )
          )
        )

Resources