I need to filter for each of the products, which are the distances that are profitable using them.
In the example below, using car the profitable distance is 3 and skate the profitable distances are 3 and 5:
| product (column A) | distance (column B) | profit/loss (column C) |
|---|---|---|
| car | 3 | -1 |
| skate | 5 | 1 |
| skate | 7 | 2 |
| car | 7 | -1 |
| skate | 7 | -3 |
| car | 3 | 3 |
| skate | 5 | -4 |
| skate | 5 | 6 |
| skate | 3 | 2 |
So the result I would like to get would be this:
| options (column E) | distances (column F) |
|---|---|
| car | 3 |
| skate | 3,5 |
Column E:
=UNIQUE(A1:A)
Column F:
=ARRAYFORMULA(IF(E1:E="","",JOIN(",",FILTER(UNIQUE(B1:B),SUMIFS(C1:C,A1:A,E1:E,B1:B,UNIQUE(B1:B))))))
I tried to use FILTER and combine with SUMIFS, but it generated error in divergent size ranges.
I would like to know if there is any way I can make this filter somehow for each of the objects and that I have the freedom to increase the number of products according to the time (that's why I combined it with ARRAYFORMULA, so I don't get stuck adding line-by-line formulas)
Edit 1:
Why 7 is not included in Profit for Skate:
Because at distance 7, we have the values 2 and -3, so it becomes a return -1
Edit 2:
Why 3,5 in Profit for Skate:
Because at distance 3, we have the value 2, so it becomes a return 2
Because at distance 5, we have the values 1, -4 and 6, so it becomes a return 3
CodePudding user response：
try:
=INDEX(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(
QUERY({A2:A&"", B2:B&",", C2:C}, "select Col1,Col2,sum(Col3) group by Col1,Col2"),
"select max(Col2) where Col3 >0 group by Col2 pivot Col1"),,9^9)), "")), ",$", ))
