google-sheetsspreadsheet

Automatic subtraction according to conditions in Google Sheets


how to make automatic subtraction vertically downwards according to conditions in Google Sheets

SKU SKU BALANCE ID CUST CUST ORDER DESIRE FORMULA
10100 100 115501 90 10
10100 100 115502 10 0
10036 100 115503 25 75
10036 100 115504 25 50
10036 100 115505 5 45
10040 100 115506 50 50
10040 100 115507 25 25
10040 100 115508 30 -5
10041 100 115519 50 DUPLICATES
10041 100 115519 50 DUPLICATES
10041 100 115519 50 DUPLICATES
10036 100 115520 45 0

https://docs.google.com/spreadsheets/d/1lJKWrmMXNtvNoVY7KiBSX-_b7yKPh6Ooa3gcnubqi3o/edit?gid=0#gid=0


Solution

  • Here's one generalized formula approach which you may adapt accordingly

    Also the formula as of now does not deal with a scenario where lets say SKU 10041 has only 2 dupe ID CUST no.s and one distinct no. or so since no clarity is given on such a thing!

    =map(A2:A,lambda(Σ,if(Σ="",,if(let(Λ,filter(C:C,A:A=Σ),counta(Λ)<>countunique(Λ)),"DUPLICATE",+filter(B:B,A:A=Σ)-sum(chooserows(filter(D:D,A:A=Σ),sequence(countif(A2:Σ,Σ))))))))
    

    enter image description here