9

In Google Spreadsheets, without using Apps Script etc., is it possible to achieve the following with a single formula, such as arrayformula (or maybe mmult or something strange, but no fill-down) at the top of Column B?:

scenario:

Column A: cells are empty or non-empty

goal:

Column B: contains the last non-empty cell of column A

example:

input | output
===============
    a | a
------+--------
      | a
------+--------
      | a
------+--------
    5 | 5
------+--------
      | 5
------+--------
    c | c
------+--------
    c | c
------+--------
      | c
------+--------
    a | a
------+--------
| improve this question | |
11

Short answer

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

Explanation

Assuming the input data in the Column A of the below table and that the above formula is in cell B1.

Note: In order to make easier to evaluate the formula, the Column A (input) doesn't repeat values but it will work with any kind of values (letters, numbers, symbols, repeated, unsorted, etc.)

      |    A       B   
  === + ======= =======
   1  |    a       a   
   2  |            a   
   3  |    b       b   
   4  |            b   
   5  |            b   
   6  |    c       c   
   7  |    d       d   
   8  |    e       e   
   9  |            e   
  10  |    f       f

VLOOKUP was selected as it could be used to find approximate values and it could return multiple values (array).

As the input values could appear in any order, instead of using the actual values, the row number was used for non empty cells and empty cells have assigned an empty string "".

It was used LEN instead of ISBLANK because cells with formulas that return an empty string return FALSE and this could cause problems in some common scenarios.

IF(LEN(A1:A10)>0,ROW(1:10),"")

NOTE: In you want to save three characters, the second parameter ("" the character is the parameter separator ,) could be removed. When analysing this part of formula empty cells will return FALSE.

As VLOOKUP requires that the lookup up column be the first column, instead of a range, a "semi-manually" made array was used.

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

The above approach save us cells and processing time.

| improve this answer | |
  • 1
    Can be made a bit shorter by omitting "" in the if command (omitted argument is blank by default). – user79865 Jan 7 '16 at 16:17
  • Ah that's clever, I see you create a temporary lookup table mapping rows to values ({IF(...), ...} consists of rows 1->a, 3->b, 6->c, ... with some blank rows), then perform a lookup. Thank you also for the explanation! However what do you mean "In order to make easier to evaluate it, the column A (input) doesn't repeat values"? This seems to handle duplicate values (a b c a e ...), doesn't it? – ninjagecko Jan 8 '16 at 8:34
  • @ninjagecko: I was talking about the sample input data. I mentioned this because the OP included repeated data. I will reword that part to make that clearer. – Rubén♦ Jan 8 '16 at 10:57
  • @Normal: I didn't omit "" but added a note about that and replaced ROW(A1:A10) by ROW(1:10). :) – Rubén♦ Jan 8 '16 at 11:38
  • 1
    @ninjagecko: Maybe the use in my answer of the term "evaluate" is causing confusion due to your background. In your Web Apps profile you don't share much about it, but I can see that you have 35k reputation in SO, so I think that you use "evaluate" frequently as something done by a machine. In my case I my first thought about it something done by humans. In this case "whom" is the formula builder/adopter, specially those who have not used before vlookup and arrays together in Google Sheets. – Rubén♦ Jan 10 '16 at 14:57
3

In addition to the previous post, below formula will allow the use of open-ended ranges so that output will 'auto-expand' when new values are added to the referenced column (A in the example below).

=ArrayFormula(
   if(row(A2:A) <= max(if(not(isblank(A2:A)),row(A2:A))),
      vlookup(row(A2:A),filter({row(A2:A),A2:A},len(A2:A)),2),
   )
 )

EDIT: if you don't want the output limited to the last row of the sourcedata, you can 'hardcode' the end row like this:

=ArrayFormula( if(row(A:A) <= 20, vlookup(row(A:A),filter({row(A:A),A:A},len(A:A)),2), ) )
| improve this answer | |
  • One caveat of this formula is that it "stops" on the last row of the data source. I think that this should be mentioned in the answer. – Rubén♦ Mar 2 '16 at 22:47
  • @Rubén: I think that can be easily amended. Let's say you want the output to continue after the last row of the source data (e.g.: row 20) you can change the formula to =ArrayFormula( if(row(A:A) <= 20, vlookup(row(A:A),filter({row(A:A),A:A},len(A:A)),2), ) ) – JPV Mar 3 '16 at 7:45
  • That should be mentioned in the answer too, I think. – Rubén♦ Mar 3 '16 at 12:22
  • 1
    @Rubén: I just did. ;-) – JPV Mar 3 '16 at 15:21
-1

You can try this formula:

=ArrayFormula(if(ISBLANK(A1:A10);INDEX(A1:A10;ROW()-1;COLUMN());A1:A10))
| improve this answer | |

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.