1

Basically, what I want to do is fill all of the blank cell in a column with the value of the previous non-blank cells. For example:

enter image description here

I was wondering if there was a way to do this without having to do the auto-fill individually for each set of cells with the same value, as I have a sheet that's thousands of entries long.

Update: I did not elaborate fully on what I'm looking to achieve, and I'm sorry for that. Here is a link to a test file: Test file

I have a script adding a new row each day, (not in the file). I want to have a array formula similar to: =ARRAYFORMULA(VLookup(Row(B2:B), {IF(B2:B="",,Row(B2:B)),B2:B},2,1))

Unfortunately, the above formula chooses the value from the above cell, not below. I hope I have clarified my desired achievement better now.

1

2 Answers 2

1

One way is to use map() and filter(), like this:

=map( 
  B2:B14, 
  lambda( 
    cell, 
    if( 
      len(cell), 
      cell, 
      iferror(+filter(cell:B14, len(cell:B14), row(cell:B14) > row(cell))) 
    ) 
  ) 
)

See your sample spreadsheet.

2
0

Welcome Marcus!

Yes, this is the way. First go to File-Settings-Calculations tab and enable Iterative calculation.

Now in cell C2 write the formula: =ARRAYFORMULA(IF($A$2:$A="",$C$1:$C,$A$2:$A))

Copy the result and with Ctrl+Shift+V (Editing - Paste Special - Values ​​only) replace the original column A. Now you can clear C2 and return Iterative calculation to OFF

Update To fill cells in column fro bottom to top type to E2 formula

=IFERROR(ArrayFormula(IF(ISBLANK(B2),OFFSET($B$1,MIN(IF(ISBLANK(B3:B),"",ROW(B3:B)))-1,0),B2)),"")

and press Ctrl+Enter to Autofill

2
  • Thanks for the welcome! I did not elaborate fully on what I'm looking to achieve, and I'm sorry for that. Here is like to a test file: docs.google.com/spreadsheets/d/… I have a script adding a new row each day, (not in the file). I want to have a array formula similar to : ARRAYFORMULA(VLookup(Row(B2:B), {IF(B2:B="",,Row(B2:B)),B2:B},2,1)) Unfortunately, the above formula chooses the value from the above cell, not below. I hope I have clarified my desired achievement better now.
    – Marcus
    Sep 22 at 14:06
  • @Marcus Oh, you mean fill from bottom to top? I have changed my answer
    – JohnSUN
    Sep 22 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.