Maths based on dynamic groups
I am creating a sheet where data in column A will be input using a barcode scanner, and I want the sheet to recognize where one group of scans ends and the next begins, which will not be the same number of rows. Then do maths based on what comes after the first scan. For example, A1 will be an item barcode, A2 might be a quantity code (+/-), A3 might be either another quantity code or it could be the next item code. All of the item barcodes start with the same digits; how can I get sheets to recognize the start of a new "group" of items and do maths based on only that group? I've included a sample sheet - hope this makes sense. Prefer to do this using a formula and not VBS.
Thanks in advance!
https://docs.google.com/spreadsheets/d/1798XZ8csvQbQpAvoQGS1vJJ4gsWcYSLVXXwzhm22AmY/edit?usp=sharing
try:
=ARRAYFORMULA(IFNA(VLOOKUP(IF(REGEXMATCH(A2:A, "^06767.+"), "×"&ROW(A2:A), ),
QUERY({VLOOKUP(ROW(A2:A), IF(REGEXMATCH(A2:A, "^06767.+"), {ROW(A2:A),
"×"&ROW(A2:A)}), 2, 1), IF(ISNUMBER(C2:C), C2:C*1, )*1},
"select Col1,sum(Col2)
where Col2 is not null
group by Col1
label sum(Col2)''", 0), 2, 0)))
- How to know Sheet ID that using BigQuery Sheet Connector
- Google Sheets. How to create formula to get 10 highest values from one table and put to another one?
- Use Lambda to stack multiple query array
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria
- Conditional formatting if cell past date and another cell empty with arrayformula
- ranking formula start from negative number
- Trouble getting log function to work in a custom function for Google sheets
- Checking for an option in a multi-select dropdown
- ARRAYFORMULA SUM
- Display pasted cells temporarily then return to main page
- How can I search a Google Sheet for the cell that matches the intersection of the first column and top row of another sheet
- Javascript case sensitive
- Updating the user during functions triggered by time driven triggers
- Can I alter a formula using the cell number instead of its cell value?
- Dynamically referencing worksheets to dynamically fill a table based on a condition
- How do you add UI inside cells in a google spreadsheet using app script?
- How can I force data input into cells in Google Sheets to be in a desired date format?
- How do I find a partial match from a data table to an individual cell?
- Highlighting non-first instance duplicates of words and counting highlighted cells
- Lock google sheets for data entry after a wrong entry
- Schedule View: INDEX MATCH from table and return multiple cells into one
- How to Show/Hide rows in google sheets using a script?
- Popping a Modal Dialog when changing the drop down entry in Google Sheets
- Transfer background color from cell range to drop down options
- How can I match a smart chip using SUMIF?
- List Google drive folder contents to google sheets with only new files
- Trying to figure out why my GoogleSheets Date formula is not working
- Count if name matches and outcome is complete
- Google Sheets Row Number of last row in a defined table
- How can I use INDEX and MATCH along with conditions to filter data into different tables?