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:Σ,Σ))))))))
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script
- In Google sheets, how does one either return a blank cell or perform a function based on another cells lack of content?
- Incorrect result of iferror formula via Apps Script
- How to Extract URLs from HYPERLINK in Google Sheets
- Using a reference cell as part of a column in a google sheets query
- Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?
- Google Finance does not show last closing price after market hours. It just shows last *traded* price instead of *closing*
- How to ignore empty rows when submitting to database
- Using Named Functions with New Google Tables
- Google sheets query not returning words with special characters
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Adding multiple durations together in google sheets breaking
- Trying to figure out why my GoogleSheets Date formula is not working
- Why is this conditional formatting rule not working in Google Sheets?
- Multiple scripts combined to one script will not run
- Compare two cells and input timestamp in third if they do not match Google Apps Script
- QUERY function with multiple conditions using time frame
- Show next row as soon as a cell is filled, using a macro
- Why does my Google Sheets query simplificaiton result in an error?
- Comparing Dates between 2 Sheets, Not Evaluating as true when conditions are met
- Highlighting duplicates in Google Sheets (from a source of 1 row, not a column)
- I want to repeat a line item in a google sheets form response based on a date value for consecutive dates
- Daytime and Nightime Occurance Duration of an Event
- I do not understand the error message displayed when I try to use QUERY() function in Google Sheets
- In Google Sheets, how do I keep a cell blank based on two other cells' contents?
- Getting Sheet to Modify It with Apps Script