Home > Software engineering >  Filter the products where your calculated options generate a profit result
Filter the products where your calculated options generate a profit result

Time:11-02

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)), "​")), ",$", ))

enter image description here

  • Related