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

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

3
  • 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, 2022 at 14:06
  • @Marcus Oh, you mean fill from bottom to top? I have changed my answer
    – JohnSUN
    Sep 22, 2022 at 16:18
  • your updated solution also worked. Thank you for your time and effort. I'm going with doubleunary solution since it filled the entire column. It's more convenient in my make.
    – Marcus
    Oct 6, 2022 at 9:09
2

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.

1
  • Thanks a bunch @doubleunary, that worked like a treat! I do not really know what this formula does, but I managed to make it work in my sheet:D
    – Marcus
    Sep 22, 2022 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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