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 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
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), "" ) ) ) ) )
What do the data look like? Do you have multiple scores for the same week?
- rtayerstCopper Contributor
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), "" ) ) ) ) )