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 :
- 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
- Multiple submission at exact same time in Google App Script
- Hello world menu and sidebar for Google Workspace Add-On for Google Sheets
- Did Google Sheets stop allowing json access?
- How to Run Script on Multiple Spreadsheet Tabs
- Filtering data from other google Spreadsheet sheet based on a combo value from other sheet in the same spreadsheet
- Can I count occurrences of each unique item and then sort by that count
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Apps Script: why does name of properties cause "Maximum call stack size exceeded"?
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets