google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
Custom Serial Numbering Formula
In Google Sheets,
I want to achieve a serial numbering system in a specific manner which satisfies the following requirements:
- Serial Numbers should be in reverse order (In Descending Order).
- Only the rows which have non-empty value in Column-B will get a serial number.
- Adding any non-empty value in Column-B makes that row eligible for having a serial number. Hence, making Column-B blank will remove its serial number and adjust all the serial numbers on rows above the modified one.
Test Cases:
- Leaving a row blank in between filled rows, should not affect the serially numbered sequence.
Can anyone please provide a formula or steps to implement this feature in Google Sheets?
Solution
use this formula:
=ARRAYFORMULA(IFERROR(VLOOKUP(ROW(B:B)&B:B, QUERY({SORT({ROW(
INDIRECT("B1:B"&COUNTA(B:B))), FILTER(ROW(B:B)&B:B, B:B<>"")}, 1, 0), ROW(
INDIRECT("B1:B"&COUNTA(B:B)))}, "select Col2,Col3", 0), 2, 0)))
- Error message while automating data entry from web app contact form to google sheet
- How do I send email to user by appending my domain name to the user name in Google Apps Script?
- How to split comma-separated values across multiple rows dynamically?
- Replace multiple strings in Google Spreadsheet with script
- How to count the amount of times a substring appears in a range of cells?
- Query with multiple data types
- Count If Range Matches Lookup in another tab
- Google Sheets Query where data in either of two columns matches the value in a cell populated by a multi-select dropdown
- Trying to combine words and math in a single cell in google sheets
- Counting the previous sample over multiple conditions throughout the range
- How to randomly select by type probability all items from a google sheet column range
- Sort result of function by RHS column
- Google Sheets Query - using another query to filter dates
- Skip formula if sheet does not exist in Google Sheets
- How to get data from public google sheet and more than 100 rows
- In Google sheets how can I Script the manipulation of the default Zoom?
- Google Sheets IMPORTDATA for xlsx files
- conditional formatting to highlight the highest value in a row compared to all the columns before it
- Google sheets summation formula, iterating until a new header on column A
- Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block
- How to scroll to bottom of a google spreadsheet using selenium
- Calculate average days between dates continuously updating
- Conditional formatting in Google Sheets is recognizing some cell values that equal 0, not others
- Model / Year formula fails when a sequence starts stops then starts again
- Google Sheets: Sumif formula
- Randomize cells in Google Sheets
- Google script for move rows depending on checkbox - Google sheet
- How do I splitTextToColumns only for cells containing commas in Google Apps Script?
- Getting a “Google hasn’t verified this app” warning when creating a trigger - ok to ignore it?
- Count in specific ways in Google sheets