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
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Add title row with ARRAYFORMULA in Google Sheets
- Why does my Google Service Account not see files shared with it, but can see files created with it?
- Google Sheets macro to copy a line of data to next empty row on another sheet
- Google Sheets - Auto border to a Dynamic Data
- Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first column's title
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Split cell with dates and find matching dates on headers
- How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets
- How can I determine if a respondent's email address is unique in a list?
- ARRAYFORMULA in GOOGLE SHEETS
- Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger?
- API call to directory.members.insert failed with error: Resource Not Found: groupKey
- How to find common words in two columns in Google Sheets
- Get list of unique words without numbers and symbols from paragraph range in Google Sheet?
- Apps Script Delete Image
- App Script to download specific cells in Google Sheet to PDF
- Export Google Docs comments into Google Sheets, along with highlighted text?
- INDIRECT function in an INDEX-MATCH-MATCH
- How to write to a "Google Spreadsheet" from Excel 2003 VBA
- How to use date with LIKE inside query?
- Know if the cell is being used in any formula