How to create array-based sequential numbering in Google Sheets based on adjacent column, skipping blanks?
In Google Sheets, how can I create an ARRAYFORMULA to generate sequential numbering in column B, only when adjacent C cell is not empty?
- I want to start sequential numbering from B10,
- If a cell in column C is blank, numbering in B cell be skipped, and then it continues from the next non-empty C cell.
Example:
| Column B | Column C |
|---|---|
| 1 | apple |
| 2 | mango |
| | |
| 3 | orange |
| 4 | peach |
I have found formulas for sequential numbering that ignore blank cells in the adjacent column, but these formulas need to be dragged down whenever new rows are added. I want to use ARRAYFORMULA so that it applies automatically to the entire range.
I tried using this formula generated by Claude:
=ARRAYFORMULA(IF(C10:C<>"", ROW(C10:C) - ROW(C10) + 1, ""))
This formula returns a blank in B cell when the adjacent C cell is empty, but it continues the numbering. So, in the table, it would show 4 - oranges , and , 5 - peach.
Here's one approach which you may adapt accordingly:
=map(C2:C,lambda(Σ,if(Σ="",,counta(C2:Σ))))
Alternative to try just in case those blank cells in Column_C are not true blank cells (which gives ISBLANK(<blank cell>)=false)
=map(C2:C,lambda(Σ,if(Σ="",,let(Σ,C2:Σ,counta(filter(Σ,Σ<>""))))))
- Google Sheets - returning Nth result with multiple matching criteria
- Save Google sheet data onEdit in other sheet update duplicate data
- IF Status Formula Google Sheet Approval
- Google apps script: autoResizeColumn works incorrectly not works as expected
- How to find common words in two columns in Google Sheets
- Unable to open Google xlsx spreadsheet / Also Google Drive permission Blocked
- Turn a list of dates into Fridays Only
- Calculate difference of values in specific range of time in Excel
- Select from series of columns without blanks and then where Google Sheets query
- Allow user with edit privileges to run scripts on a spreadsheet owned by me
- Delete last row with value then find
- How can I save the URL(s), after making a copy (event) of a Google Sheets spreadsheet?
- How to make previous dates data from one tab appear in another tab
- Is it possible to generate a summary table of another table?
- Replace form response email address with name from master list
- How do I show data from a spreadsheet? it shows "undefined"
- Extract multiple sparsely packed responses to yes/no identifiers while preserving row information
- getLastRow() in Apps Script returns error: "This operation is not allowed on cells in typed columns."
- I can't convert 46809654.36 into a date and time value in Google Sheets
- Google Spreadsheet Last X Rows
- Select rows that contain a specific dropdown value
- How to negate 'isblank' function
- Is it possible get Image from Google Sheets to Google Docs Template when creating
- Byrow formula with reference to last row and last column
- Dynamically update variable in Spreadsheet popup UI element from google Apps Script
- Save timestamp time in a cell and then the timestamp edit time in another cell
- find a column where specific rows contain specific values
- How can I ensure my Google Apps Script merges cells in the final rows as expected?
- Inserting images into a Sheet as BlobSource using Google Apps Script
- Slack to Google Sheets integration "dispatch failed" error from slash command