Forum Discussion

rtayerst's avatar
rtayerst
Copper Contributor
Nov 04, 2024
Solved

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 ca...
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), 
          ""
        )
      )
    )
  )
)
HansVogelaar's avatar
HansVogelaar
MVP
Nov 04, 2024

rtayerst 

What do the data look like? Do you have multiple scores for the same week?

  • 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), 
          ""
        )
      )
    )
  )
)
      • rtayerst's avatar
        rtayerst
        Copper Contributor
        Nov 04, 2024

        HansVogelaar 

         

        Thank you so much...LET, and LAMDA functions are two I need to learn!

Resources