My question is inspired by this video: https://www.youtube.com/shorts/JZewxiKFplk

An Excel Table with data of names (and other data in adjacent columns irrelevant to this question). The Excel Table has data at different rows and there can be blanks arbitrarily in the table.

Like the SEQUENCE function featured in youtube video above, I am looking for a function that will generate in the column left of the Excel Table numbering for each row of data contained in the Excel Table. But unlike the youtube video above, the numbering should skip rows where there is no data, and continue its consecutive numbering for each row that contains data.

In the example below, left of "John" would be 1, left of "Mary" would be 2, and left of "Tom" would be 3. I'm not looking for a solution that would require I enter an Excel formula at every row of the Excel Table. I'm looking for a single formula like what's featured in the youtube video above. The formula should also be non-volatile.

Does a solution exist? As a last resort, if no Excel formula can do this, would a VBA solution work? The Excel Table will change size depending on the amount of data. Thank you very much for your help.

Example Excel Table of Data | Name | | -------- | | | | John | | | | Mary | | | | | | | | Tom | | | | |

CodePudding user response：

Assuming your names are in B2:B20:

=LET(ζ,B2:B20,IF(ζ="","",SCAN(0,ζ,LAMBDA(α,β,α (β<>"")))))