Currently working on a project, using it as a learning tool. I’ve been working at this problem for hours for what seems to be a simple thing but cant get past
Working with data in A2:E with a header in row 1
I need to add column C based on the uniqueness of column D and column A filtered by the value in cell H2 and the results put into one cell (I2)
My current formula in I2 is
=QUERY(FILTER(C2:D,B2:B=H2),"select Col2, count(Col2) where Col2 is not null group by Col2 label count(Col2) ''",0)
that gets me this results:
| c - Cup | 1 |
| w - Whole | 1 |
the result I want is simple
| c - Cup 1, w - Whole 1 |
here is the spreadsheet:
https://docs.google.com/spreadsheets/d/1nMvpJJ5rEptrFOZni91_c0slAJmtRVQvh0Z2ybRqQNk/edit?usp=sharing
I’ve been through numerus lines of formulas. From query to simple filters. to many to list. I’ve been at this for hours
>Solution :
Added a solution here for you to test out:
=let(Δ,query(filter({map(C:C,lambda(Σ,let(Λ,split(Σ,"/"),iferror(round(index(Λ,,1)/index(Λ,,2),2),--Σ)))),D:D},B:B=G2),"Select sum(Col1),Col2 group by Col2 label sum(Col1)''"),
join(", ",byrow(Δ,lambda(z,join(" ",z)))))
