Forum Discussion
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...
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), "" ) ) ) ) )
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
| Week | Multiplier | Score |
1 | 1 | 53 |
| 1 | 2 | 68 |
| 1 | 1 | 30 |
| 2 | 2 | 83 |
| 2 | 1 | 60 |
| 2 | 1 | 49 |
| 3 | 1 | 53 |
| 3 | 1 | 80 |
| 3 | 2 | 27 |
therefore the result would be
| Week | <50 | >50 |
| 1 | 68 | |
| 2 | 83 | |
| 3 | 27 |
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),
""
)
)
)
)
)
