Google Sheets auto increment column A if column B is not empty-CodePudding

I want to auto increment column A if column B is not empty. For example if I type anything to B14, I want A14 to auto increment.

How can I do this?

CodePudding user response：

if B column is not interrupted by an empty cell you are good with just:

=SEQUENCE(COUNTA(B2:B))

CodePudding user response：

If column B is interrupted (or even not) by empty cells you could use

=INDEX(IFNA(VLOOKUP(ROW(A2:A),
    {FILTER(ROW(A2:A),(B2:B<>"")),SEQUENCE(COUNTIF(B2:B,"<>"),1)},2,0)))

CodePudding user response：

Here are some methods that may suit your needs...

This will auto-sequence while jumping blank rows, so will be the number of filled cells/rows:

=ARRAYFORMULA(IFERROR(MATCH($B$2:$B&ROW($B$2:$B),FILTER($B$2:$B&ROW($B$2:$B),$B$2:$B<>""),0)&"."))

This will auto-sequence while skipping blank rows, so the number will be the row of data (not the row number):

=ARRAYFORMULA(IF($B$2:$B<>"",ROW($B$2:$B)-ROW() 1 & ".",""))

or:

=ARRAYFORMULA(IF($B$2:$B<>"",SEQUENCE(ROWS($B$2:$B))&".",""))

And this will autofill every row up to the last row with a value:

=ARRAYFORMULA((QUOTIENT(SEQUENCE(SUMPRODUCT(MAX(($B2:$B<>"")*(ROW($B2:$B))))-1)-1,1)*1) 1)