10

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
------+--------

4 Answers 4

12

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.

7
  • 1
    Can be made a bit shorter by omitting "" in the if command (omitted argument is blank by default).
    – user79865
    Commented Jan 7, 2016 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
    Commented Jan 8, 2016 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. Commented Jan 8, 2016 at 10:57
  • @Normal: I didn't omit "" but added a note about that and replaced ROW(A1:A10) by ROW(1:10). :) Commented Jan 8, 2016 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. Commented Jan 10, 2016 at 14:57
4

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), ) )
4
  • 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. Commented Mar 2, 2016 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
    Commented Mar 3, 2016 at 7:45
  • That should be mentioned in the answer too, I think. Commented Mar 3, 2016 at 12:22
  • 1
    @Rubén: I just did. ;-)
    – JPV
    Commented Mar 3, 2016 at 15:21
3

Answering my own question many years later; the previous answers are very good, but now it's best to do this:

=SCAN(
  "", A1:A10,
  LAMBDA(acc,x,
    IF(x="", acc, x)
  )
)

SCAN iterates through a 1-dimensional array, at each step keeping track of the previous result as returned by the lambda (or exceptionally, during the very first step, the previous result is considered to be the "" parameter we specified). SCAN, considering the previous element acc and the new element x, then chooses what it will yield as the next "previous element". We specify the logic we want with an IF statement: continue to yield our remembered variable acc if the first column is blank; however, if the first column is something new, forget the old acc and start returning a new value to give to the new acc.

tl;dr: Basically the lambda directly returns the value we want (column B), as a function of the previous value (acc = one higher in column B) and the current input (x = corresponding cell in column A)

The functional programming functions like MAP and REDUCE and MAKEARRAY and BYROW and BYCOLUMN and SCAN are incredibly powerful.

You can use SCAN to do much more complicated things. In particular, you could use this as a construct for general iteration with state, but you cannot pass arrays through acc, only singleton values, so you'd have to serialize and deserialize with JOIN and SPLIT and only work with string datatypes*

*(as a general sketch, you could return a row from each lambda (disallowed, so you return the row as a string and unpack it, with the help of named functions, then repack it, to simulate variables like myVar1=INDEX(row,2) or myVar2=INDEX(row,3) etc.), and finally you take INDEX({row0;row1;row2;...;rowN},,1) where {...} is the result of SCAN, with the convention that the leftmost elements of the row are the ones you 'print').(actually as of today, it seems returning rows is now supported? see addendum for a sketch of some more advanced functionality)

Lambdas seem to be a bit buggy or not generally powerful enough to do iteration with recursion e.g. Y-combinator.

You could use Apps Script for all this if you need something more complicated, but then it wouldn't be "instant speed" and might buggily freeze forever with no workaround because of a AppsScript server hiccup, and then you're stuck.


Addendum:

You could used Named Functions to shoe-horn in your own imperative programming, with continuation-passing style. This gives you the power to sanely do more complicated things without resorting to 'tricks'.

enter image description here

=ITERATE(
  {
    ".arrIndex",0,
    ".subIndex",0,
    ".curTotal",0,
    ".subTotal",0,
    ".curCount",0
  },
  A2:A13,
  LAMBDA(s, i,x, get,set,yield,
    IF( x<>"",

      set(s,".arrIndex", get(s,".arrIndex")+IF(get(s,".subIndex")=0, 1,0),  "",LAMBDA(s,
      set(s,".subIndex", get(s,".subIndex")+1,  "",LAMBDA(s,
      set(s,".curTotal", get(s,".curTotal")+x,  "",LAMBDA(s,
      set(s,".subTotal", get(s,".subTotal")+x,  "",LAMBDA(s,
      set(s,".curCount", get(s,".curCount")+1,  "",LAMBDA(s,
      set(s,".curAvg", IFERROR(get(s,".curTotal")/get(s,".curCount")),  "",LAMBDA(s,
        yield(s, i&": at arr["&get(s,".arrIndex")&"]="&"x"&" subarr["&get(s,".subIndex")&"] we yielded "&GET(s,".curAvg") )
      )) )) )) )) )) )),

      set(s,".subIndex", 0,  "",LAMBDA(s,
      set(s,".subTotal", 0,  "",LAMBDA(s,
        yield(s, "")
      )) ))
    )
  )
)

The above code requires we defined a Named Function ITERATE to be something as shown below. It's reusable though. This is what I meant by using SCAN to "thread state" between rows in an iterative manner.

LAMBDA(seed,arr,f,

i.e. name="ITERATE", param1="seed", param2="arr", param3="f"

with function body

  LAMBDA(get,set,
    LAMBDA(yield,

    SCAN({"yield","", "i",-1, seed},arr,
      LAMBDA(acc,x,
        set(acc,"i", get(acc,"i")+1,  "",LAMBDA(s,
          f(
            s, get(s,"i"), x,
            get,set,yield 
          )
        ))
      )
    )

    )(

      LAMBDA(state,retVal,
        set(state,"yield", retVal,  "",LAMBDA(x,x))
      )

    )
  )(

    LAMBDA(state,varname,
      INDEX(state, XMATCH(varname,state)+1)
    ),

    LAMBDA(state,varname,newVal, comment,k,
      k(
        IF( COUNTIF(state,varname)=0,
          {state, varname,newVal},
          SCAN("",state, LAMBDA(last,oldVal,
            IF(last=varname, 
              newVal,
              oldVal
            )
          ))
        )
      )
    )

  )

end of last paren; ignore if you are defining a Named Function

)

You could elaborate on the above so you have an easier time yielding a YIELD(s, {"row","of","values"}) rather than a singleton, but the above is a proof of concept.

2
  • It will very nice if you make a brief mention about how you found the new fuctions like SCAN, LAMBDA, etc. Commented Nov 20, 2022 at 17:16
  • @Rubén: On Aug 24th there was a post at workspaceupdates.googleblog.com/2022/08/… (and a similar one on Ben Collins' blog which was social media auto-recommended to me). There're two more functions XLOOKUP (seems like a much saner LOOKUP equivalent to LOOKUP(query, {queryList,returnList}, 2, etc) with support for limsup and liminf) and XMATCH ("arg-"match in mathspeak). MAP etc. are common in functional programming. Similar functions were added to Excel around 2021 apparently, but I don't follow that. Thanks for the original answer. =)
    – ninjagecko
    Commented Nov 21, 2022 at 19:05
-1

You can try this formula:

=ArrayFormula(if(ISBLANK(A1:A10);INDEX(A1:A10;ROW()-1;COLUMN());A1:A10))

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.