google-sheetsgoogle-sheets-formulaspreadsheet

Google Sheet, need help calculating difference with multiple conditions


https://docs.google.com/spreadsheets/d/1w7d2w1duZ9EtNFS7bkCthCMWMWLqYJtDGergeY__fns/edit?usp=sharing

I have this table of data in google sheet where I want to calculate the difference of credit and debit value with several conditions. The condition are as follow :

  1. The difference are calculated by deducting credit with debit amount of the same id no.
  2. The credit might contain multiple id no, while every debit only has 1 id no.

I have attached the sample sheet and the result which i want to achieve.


Solution

  • Here's one approach you may test out:

    =map(K3:index(K:K,match(,0/(K:K<>""))),lambda(Σ,let(a,C1:offset(Σ,-1,-8),b,J1:offset(Σ,-1,-1),Λ,offset(Σ,,-8),if(or(Σ="",and(countif(C1:Λ,Λ)=1,iserr(find("/",Λ)))),,
     Σ-xlookup(Λ,a,b,sum(index(xlookup(split(Λ,"/"),a,b,,,-1))),,-1)))))
    

    enter image description here

    DISCLAIMER: the formula does not search multiple code types (such as LY20851108(1)/LY20851108A), if their individual debits are zero so that output should be marked as null. As of now it does search for single_codes like 85U01U1210; whose output is marked for null