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