Custom Serial Numbering Formula

1
votes

enter image description here

In Google Sheets,

I want to achieve a serial numbering system in a specific manner which satisfies the following requirements:

  1. Serial Numbers should be in reverse order (In Descending Order).
  2. Only the rows which have non-empty value in Column-B will get a serial number.
  3. 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:

  1. 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?

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

2 Answers

3
votes

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)))

0

2
votes

Try this:

If you need to reflect the alphabetical order of column B:

=ArrayFormula(if(B:B<>"",counta(B:B)+1-match(B:B,sort(B:B,1,TRUE),0),))

If you just need to reflect the index / position of the value in column B:

=ArrayFormula(if(B:B<>"",counta(B:B)+1-match(B:B,filter(B:B,B:B<>""),0),))

Not sure about the spec #3, though, so I may have missed something.

If that doesn't do the trick, can you please describe what you've tried already, and maybe share an example spreadsheet that doesn't contain confidential info, and that would contain the expected result for each of your use cases?