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

Excel
  • HansVogelaar's avatar
    HansVogelaar
    Nov 04, 2024

    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
      Nov 04, 2024

      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 

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 04, 2024

        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