I would like to do something similar to what was solved in the question:
An arrayformula to find the previous non-empty cell in another column
The problem was:
If I have a column "input", I would like the column "ouput" to be filled with the preceding value of the "input" column.
input - ouput
a a
a
a
b b
b
b
b
c c
c
c
c
It was a great answer, however, the ArrayFormula works with an user-specified range:
=ArrayFormula(vlookup(ROW(1:10),{IF(LEN(A1:A10)>0,ROW(1:10),""),A1:A10},2))
How would I make the function to automatically get the last row that it has to take?
Specifically, I would like to know how to get automatically the number 10 of the last function.
I have tried to use INDIRECT but it finds an error.
=ArrayFormula(vlookup(ROW(INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1))),{IF(LEN(INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1))>0,ROW(INDIRECT("3:"&INDEX(SORT(B:B,ROW(B:B),FALSE),1)),""),INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1)},2)))))
This part: INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1))
is used to get the last value of a column.
I have created a spreadsheet with the functions: https://docs.google.com/spreadsheets/d/15jbXZbixRYxJSRQuKgBU8BWN6KYNjwwT5PzvYp986Ww/edit#gid=0
Any help would be appreciated.
=ArrayFormula(vlookup(ROW(B3:B),{IF(LEN(B3:B)>0,ROW(B3:B),),B3:B},2))