How to grab the last non empty row?-CodePudding

So here's the scenario. In this case i will start at row 2 as starting point. in Column B, i want to record row of the last non empty A column. For example : the first data in B column is 2 which is the very first row then in the next row it will keep that '2' as long as the A column is empty until i reach a value (1) in the A column. When it reach the next non empty row in A column (row 7), then value in B now will keep that value (7) and it will keep that value all the way down until it reach the next non empty row in A , which is row 15. etc. Hope i can explain it clearly.

for now i only use basic formula in B2 cell :

=if( A2<>1,  min( row(A2), indirect( "b" & (row(A2) -1)  ) ) ,  row( A2) )

and then copy it down to other cells in B column. It works. But i'm just want to convert this into arrayformula() and got no luck. Does anyone know how to make this works using arrayformula ?

CodePudding user response：

Try this:

=ArrayFormula(vlookup(row(A2:A24),query({row();ArrayFormula(value(substitute(A3:A24,1,row(A3:A24))))},"select * where Col1>0"),1,true))

It has a few stages: First it takes row number from the first cell using row(). Then it substitutes all 1 cells in column A into corresponding row numbers. Then using query I remove empty or 0 values. I got a small table of:

2 7 15 19

Next stage is to take each row number from a2:a24 and vlookup through my table. Using vlookup with 'true' parameter it returns nearest value from the table that is smaller than row number tested. So 2 returns 2, 3 returns 2, 4 returns 2, etc.

CodePudding user response：

use:

=INDEX(VLOOKUP(ROW(A2:A), {2; FILTER(ROW(A3:A), A3:A<>"")}, 1, 1))