Home / Questions / 5231
Accepted
Rubén
Asked: 2020-03-05 14:43:49 +0800 CST 2020-03-05 14:43:49 +0800 CST

Formula to fill in the blank cells in a column of a Google spreadsheet

  • 772

I have data in a column of a google spreadsheet with blank cells in between. I want those blanks to be filled in automatically.

In the following table, column A shows the input data and column B the expected result.

+----+---+---+
| | To | B |
+----+---+---+
| 1 | to | to |
| 2 | | to |
| 3 | | to |
| 4 | b | b |
| 5 | | b |
| 6 | c | c |
| 7 | | c |
| 8 | d | d |
| 9 | | d |
| 10 | | d |
+----+---+---+

In Excel I used in the first blank cell, in this case A2, I placed a formula like the following =A1. Then I copied it, selected the range, and the command select empty cells and paste. Google spreadsheet does not have this command included "out of the box".

I have the following array formula but it involves adjusting the references for each case.

=ArrayFormula(vlookup(ROW(1:10),{IF(LEN(A1:A10)>0,ROW(1:10),""),A1:A10},2))

Is there an array formula with a minimum of parameters that have to be adjusted in each case?

Attribution
The formula was previously posted by me as a response to An arrayformula to find the previous non-empty cell in another column .

This was later referenced in An arrayformula to find the previous non-empty cell in another column and find the last row to work with by EumirAMC which, along with my personal experience, inspired this question.

array

3 Answers

  1. I think I have a simple way to implement what you ask for. From the sheet code editor paste this code:

    function AutoFill(values)
{
  var last = "";

  for(var i = 0 ; i < values.length ; ++i)
  {
     if(values[i] == "")
       values[i] = last;
     else
       last = values[i];
  }

  return values;
}

    And from the sheet do it in the box B1 = AutoFill(A1:A10)

    If you have a problem, say so.

    As before, I found the question interesting and I have made another post on my blog: http://googleappscriptsweb.blogspot.com.es/2016/06/como-autorellenar-campos-de-un.html

    Yes, now there is a link to the question and the profile of the person asking it :D

    See you

    • 2
  2. Best Answer

    Formula without comments to "copy and paste"

    =ArrayFormula(ARRAY_CONSTRAIN(vlookup(ROW(A:A),{IF(LEN(A:A)>0,ROW(A:A),""),A:A},2),10,1))

    Explanation

    The explanation is included as a "code comment". The formula that can be "copied and pasted" is at the beginning. A variant is included at the end.

    simple formula

    =ArrayFormula( // Array formula.
   ARRAY_CONSTRAIN( // Limit the size of the final result.
     VLOOKUP( // Look up vertical. Will return an array. In a
                                         // array formula works like a loop.
       ROW(A:A), // Indicates that all rows should be searched.
                                         // By not specifying a final row the limit
                                         // set with ARRAY_CONSTRAIN.
                                         // by its row number.
       {IF(LEN(A:A)>0,ROW(A:A),""),A:A}, // Returns a two-column array. The
                                         // first indicates the row number of the  
                                         // rows with a value and empty in the cells without
                                         // value and the second is a "copy" of
                                         // column A
       2 // Indicates the column to get the value from.
    ),                                   
    10, // Indicates the number of rows to be obtained.
                                         // It is the limit of the loop.
                                         // This would be the only value to edit manually.
    1
  ) // Indicate the number of columns you want
                                         // get.
)

    Variant

    Instead of indicating the number of rows as a fixed value, in English they would say "harcoded", the reference to cell A10 is indicated. If more rows are required, insert as many as are necessary between the last cell with a value and cell A10.

    =ArrayFormula(ARRAY_CONSTRAIN(vlookup(ROW(A:A),{IF(LEN(A:A)>0,ROW(A:A),""),A:A},2),A10,1))
    • 1

  3. Thanks a lot. This solution has also helped me.

    I was trying to use that formula in a google script as follows:

    cell.setFormula("=ArrayFormula(ARRAY_CONSTRAIN(vlookup(ROW(B:B),{IF(LEN(B:B)>0,ROW(B:B),""),B:B},2),3,1))");

    But it throws me:

    Missing ) after argument list. (line 5, file "Test")

    I think, I'm not sure what error appears for the quotes? Something like this happened in VB but I solved it with more quotes.

    Thank you very much for your help.

    • 0