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 :
- The difference are calculated by deducting credit with debit amount of the same id no.
- 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)))))
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
- Allow script to edit locked cells
- How to join unique strings in their own column with QUERY?
- How do I use XLOOKUP for multiple criteria?
- How to use AppendRow
- How to code "Next Friday" in Google Sheets?
- How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?
- Casting method asTextItem throws error for Google form item
- Counting blank cells by arrayformula
- ARRAYFORMULA with GOOGLEFINANCE function
- Conditional formatting depending on two cells
- How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
- Returning the last input entry
- Draw and filter Charts in App Script Web app
- Google Sheets - select "most recent " cell in a column and return corresponding value in same row
- Byrow formula with reference to last row and last column
- How Do I Give rows in a table of contents a key. Then look up the key Later using the data from the rows in table of contents?
- Duplicate whole spreadsheet using Google Sheets API
- Array Formula to omit certain values
- Google Sheets Query While Removing Duplicates
- NAME error hiperlink function Apps Script Google Sheets
- Three-dimensional referencing
- Can I use a named range as column headers?
- Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math
- How to get the price of a stock at a particular date from Google Finance?
- How to Split a String Up into Individual Characters
- How to integrating Google Sign-In into your google script web app
- Formula is currently double-counting a cell
- Get dropdown options in google sheets in table
- Search for targets and indicate where they come from
- One-to-one assignment in Google sheets with data validation