Selecting the last value of a column

Question

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.

Something like:

=LAST(G2:G9999)

except that LAST isn't a function.

Wessex

Answer 1

So this solution takes a string as its parameter. It finds how many rows are in the sheet. It gets all the values in the column specified. It loops through the values from the end to the beginning until it finds a value that is not an empty string. Finally it retunrs the value.

Script:

function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return values[lastRow - 1];
}

Usage:

=lastValue("G")

EDIT:

In response to the comment asking for the function to update automatically:

The best way I could find is to use this with the code above:

function onEdit(event) {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue(lastValue("G"));
}

It would no longer be required to use the function in a cell like the Usage section states. Instead you are hard coding the cell you would like to update and the column you would like to track. It is possible that there is a more eloquent way to implement this (hopefully one that is not hard coded), but this is the best I could find for now.

Note that if you use the function in cell like stated earlier, it will update upon reload. Maybe there is a way to hook into onEdit() and force in cell functions to update. I just can't find it in the documentation.

Shostakovich

This is a Google Spreadsheet script. You can create a new script under Tools -> Scripts -> Script editor... – Shostakovich

:O that is amazing! I didn't know Google had implemented scripts. Thank you very much, it worked perfectly – Wessex

any idea how to make it update automatically when I change the data in the sheet? – Wessex

I edited my answer with some information that should help. Happy coding! – Shostakovich

I've made an edit to this script so you can pass named sheets into it like lastValue("Sheet1!A") gist.github.com/2701061 – Topology

Answer 2

Similar answer to caligari's answer, but we can tidy it up by just specifying the full column range:

=INDEX(G2:G, COUNT(G2:G))

Suffumigate

This appears to work as long as there are no blanks in the dataset – Heikeheil

Another good resource is the selected answer at productforums.google.com/forum/#!topic/docs/p3t3feg7Jic which shows how to get the first, last, or nth row in a FILTER()ed range, similar to @Geta's answer. – Siderite

This answer https://mcmap.net/q/116376/-get-the-last-non-empty-cell-in-a-column-in-google-sheets has a formula that supports blanks cells. – Anatomy

Or =INDEX(G2:G10; COUNT(G2:G10)) if you're trying to put this in the same column. Just put it outside the defined range. – Summerville

As @KeithSirmons mentioned. It there are blanks in your column it doesn't work, because COUNT(G2:G) returns 0. Instead use COUNTA(G2:G) that counts only the number of values in a dataset. COUNTA is only useful combined with INDEX to get the last value if there is not a single blank between your values. – Demonstrable

Also note if your values are not numeric, you'll need COUNTA. COUNT only counts numeric values: it will return 0 when given e.g. text cells. – Hemp

The comma should be a semicolon, otherwise I got formula parse error from Google Spreadsheet. =INDEX(G2:G; COUNT(G2:G)) – Cotopaxi

Another option to extract values greater than 0 =INDEX(G2:G, COUNTIF(G2:G,">0")) – Supple

Answer 3

Actually I found a simpler solution here:

http://www.google.com/support/forum/p/Google+Docs/thread?tid=20f1741a2e663bca&hl=en

It looks like this:

=FILTER( A10:A100 , ROW(A10:A100) =MAX( FILTER( ArrayFormula(ROW(A10:A100)) , NOT(ISBLANK(A10:A100)))))

Kemberlykemble

Wow this is incredibly clean. Can you explain exactly how it works? – Wessex

Not really: it is right just when source range is already sorted. – Vastha

Explained: ROW returns the row number, so FILTER( ROW(A10:A100), NOT(ISBLANK(A10:A100) ) returns an array of all the non-blank row numbers (not their values), for example [1,2, 3, 7, 12, 14]. Then MAX gives us the last row number. A second FILTER is then applied to filter out all rows where the row number doesn't match the value from MAX (i.e. the value of the last non-blank row). – Teratogenic

Both instances of ROW can be replaced by COLUMN in order to return the last value in a particular row (instead of the last value in a particular column). – Observation

What if I just wanted the row number of the last value of the column? – Galvanism

@Nick5a1, then you remove the extra (outter) FILTER since the =MAX(...) is the answer you are asking for. – Metagnathous

Answer 4

LAST() function is not implemented at the moment in order to select the last cell within a range. However, following your example:

=LAST(G2:G9999)

we are able to obtain last cell using the couple of functions INDEX() and COUNT() in this way:

=INDEX(G2:G; COUNT(G2:G))

There is a live example at the spreedsheet where I have found (and solved) the same problem (sheet Orzamentos, cell I5). Note that it works perfectly even refering to other sheets within the document.

Vastha

This works great, and auto-updates as the sheet changes. Thanks! – Psychosomatic

I like the conciseness of this suggestion, but it doesn't seem to work when the target range includes some blank cells (as mentioned in the original question above), since COUNT() doesn't count blank cells. – Observation

@JonSchneider you can use COUNTA in that case if you're sure there is not a single blank between the values in the column. See my comments to dohmoose's answer. – Demonstrable

Why does :G denote the last element? Is this documented? – Phylactery

Answer 5

Summary:

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , COUNTA(G2:G) )

Details:

I've looked through and tried several answers, and here's what I've found: The simplest solution (see Dohmoose' answer) works if there are no blanks:

=INDEX(G2:G; COUNT(G2:G))

If you have blanks, it fails.

You can handle one blank by just changing from COUNT to COUNTA (See user3280071's answer):

=INDEX(G2:G; COUNTA(G2:G))

However, this will fail for some combinations of blanks. (1 blank 1 blank 1 fails for me.)

The following code works (See Nader's answer and jason's comment):

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , ROWS( FILTER( G2:G , NOT(ISBLANK(G2:G)) ) ) )

but it requires thinking about whether you want to use COLUMNS or ROWS for a given range.

However, if COLUMNS is replaced with COUNT I seem to get a reliable, blank-proof implementation of LAST:

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , COUNT( FILTER( G2:G , NOT(ISBLANK(G2:G)) ) ) ) 

And since COUNTA has the filter built in, we can simplify further using

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , COUNTA(G2:G) )

This is somewhat simple, and correct. And you don't have to worry about whether to count rows or columns. And unlike script solutions, it automatically updates with changes to the spreadsheet.

And if you want to get the last value in a row, just change the data range:

=INDEX( FILTER( A2:2 , NOT(ISBLANK(A2:2))) , COUNTA(A2:2) )

Collected

Super helpful to walk through the steps and functionality+limitations of each iteration. I needed to to get the last value in each row, some with multiple blanks. This worked like a charm. Thank you! – Ellary

Answer 6

In order to return the last value from a column of text values you need to use COUNTA, so you would need this formula:

=INDEX(G2:G; COUNTA(G2:G))

Analgesic

But only if there is not a single blank between your values. If there is, COUNTA combined with INDEX won't return the last value in the column. – Demonstrable

Answer 7

try this: =INDIRECT("B"&arrayformula(max((B3:B<>"")*row(B3:B))))

Suppose the column in which you are looking for the last value is B.

And yes, it works with blanks.

Czarist

Answer 8

This one works for me:

=INDEX(I:I;MAX((I:I<>"")*(ROW(I:I))))

Voluntary

A nice solution, which works in the presence of empty cells, too. – Corporal

Answer 9

It looks like Google Apps Script now supports ranges as function parameters. This solution accepts a range:

// Returns row number with the last non-blank value in a column, or the first row
//   number if all are blank.
// Example: =rowWithLastValue(a2:a, 2)
// Arguments
//   range: Spreadsheet range.
//   firstRow: Row number of first row. It would be nice to pull this out of
//     the range parameter, but the information is not available.
function rowWithLastValue(range, firstRow) {
  // range is passed as an array of values from the indicated spreadsheet cells.
  for (var i = range.length - 1;  i >= 0;  -- i) {
    if (range[i] != "")  return i + firstRow;
  }
  return firstRow;
}

Also see discussion in Google Apps Script help forum: How do I force formulas to recalculate?

Farnham

Answer 10

I looked at the previous answers and they seem like they're working too hard. Maybe scripting support has simply improved. I think the function is expressed like this:

function lastValue(myRange) {
    lastRow = myRange.length;
    for (; myRange[lastRow - 1] == "" && lastRow > 0; lastRow--)
    { /*nothing to do*/ }
    return myRange[lastRow - 1];
}

In my spreadsheet I then use:

= lastValue(E17:E999)

In the function, I get an array of values with one per referenced cell and this just iterates from the end of the array backwards until it finds a non-empty value or runs out of elements. Sheet references should be interpreted before the data is passed to the function. Not fancy enough to handle multi-dimensions, either. The question did ask for the last cell in a single column, so it seems to fit. It will probably die on if you run out of data, too.

Your mileage may vary, but this works for me.

Mascarenas

Answer 11

function lastRow(column){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = sheet.getLastRow();
  var lastRowRange=sheet.getRange(column+startRow);
  return lastRowRange.getValue();
}

no hard coding.

Final

Answer 12

This gets the last value and handles empty values:

=INDEX(  FILTER( H:H ; NOT(ISBLANK(H:H))) ; ROWS( FILTER( H:H ; NOT(ISBLANK(H:H)) ) ) )

Boogie

works for columns too. I just substituted a few things. =INDEX( FILTER( 88:88 , NOT(ISBLANK(88:88))) , columns( FILTER( 88:88 , NOT(ISBLANK(88:88)) ) ) ) – Stonyhearted

generic solution: =INDEX( FILTER( indirect(row()&":"&row()) , NOT(ISBLANK(indirect(row()&":"&row())))) , columns( FILTER( indirect(row()&":"&row()) , NOT(ISBLANK(indirect(row()&":"&row()))) ) ) ) – Stonyhearted

To me, this is the "right" answer. Reasonably compact and easy to understand, and works with blanks. – Intrench

Answer 13

In a column with blanks, you can get the last value with

=+sort(G:G,row(G:G)*(G:G<>""),)

Paxton

A nice solution. I suspect @Pedro's variant (using INDEX and MAX) may be slightly more efficient than using SORT, though. – Corporal

Answer 14

The answer

$ =INDEX(G2:G; COUNT(G2:G))

doesn't work correctly in LibreOffice. However, with a small change, it works perfectly.

$ =INDEX(G2:G100000; COUNT(G2:G100000))

It always works only if the true range is smaller than (G2:G10000)

Thorr

This post was tagged "Google Spreadsheet" and "Google Apps Script", nothing related to Libre Office... you're off topic – Jeramey

Not terribly far off topic as the question's title, Selecting the last value of a column, could easily be found by some poor soul using LibreOffice or Numbers. I'm a spreadsheet amateur who knows enough to be dangerous. What I can gather is that Google has made it so that if your second argument to a range is just a column, it will select the remainder of the column so people don't have to use the very large number hack you discovered. – Uttasta

Answer 15

Is it acceptable to answer the original question with a strictly off topic answer:) You can write a formula in the spreadsheet to do this. Ugly perhaps? but effective in the normal operating of a spreadsheet.

=indirect("R"&ArrayFormula(max((G:G<>"")*row(G:G)))&"C"&7)


(G:G<>"") gives an array of true false values representing non-empty/empty cells
(G:G<>"")*row(G:G) gives an array of row numbers with zeros where cell is empty
max((G:G<>"")*row(G:G)) is the last non-empty cell in G

This is offered as a thought for a range of questions in the script area that could be delivered reliably with array formulas which have the advantage of often working in similar fashion in excel and openoffice.

Statutory

Answer 16

function getDashboardSheet(spreadsheet) {
  var sheetName = 'Name';
  return spreadsheet.getSheetByName(sheetName);
}
      var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);  
      var dashboardSheet = getDashboardSheet(spreadsheet);
      Logger.log('see:'+dashboardSheet.getLastRow());

Raffia

Answer 17

I was playing with the code given by @tinfini, and thought people might benefit from what I think is a slightly more elegant solution (note I don't think scripts worked quite the same way when he created the original answer)...

//Note that this function assumes a single column of values, it will 
//not  function properly if given a multi-dimensional array (if the 
//cells that are captured are not in a single row).

function LastInRange(values) 
{
  for (index = values.length - 1; values[index] == "" && index > 0; index--) {}
  return String(values[index]);
}

In usage it would look like this:

=LastInRange(D2:D)

Narcoma

Answer 18

I found another way may be it will help you

=INDEX( SORT( A5:D ; 1 ; FALSE) ; 1 ) -will return last row

More info from anab here: https://groups.google.com/forum/?fromgroups=#!topic/How-to-Documents/if0_fGVINmI

Savanna

Not really: it is right just when source range is already sorted. – Vastha

Answer 19

Regarding @Jon_Schneider's comment, if the column has blank cells just use COUNTA()

=INDEX(G2:G; COUNT**A**(G2:G))

Sianna

No, that doesn't work either. The difference between COUNT and COUNTA is in the distinction between numbers and text, which is irrelevant here. – Staffordshire

Answer 20

Found a slight variation that worked to eliminate blanks from the bottom of the table. =index(G2:G,COUNTIF(G2:G,"<>"))

Summerville

Answer 21

I'm surprised no one had ever given this answer before. But this should be the shortest and it even works in excel :

=ARRAYFORMULA(LOOKUP(2,1/(G2:G<>""),G2:G))

G2:G<>"" creates a array of 1/true(1) and 1/false(0). Since LOOKUP does a top down approach to find 2 and Since it'll never find 2,it comes up to the last non blank row and gives the position of that.

The other way to do this, as others might've mentioned, is:

=INDEX(G2:G,MAX((ISBLANK(G2:G)-1)*-ROW(G2:G))-1)

Finding the MAXimum ROW of the non blank row and feeding it to INDEX

In a zero blank interruption array, Using INDIRECT RC notation with COUNTBLANK is another option. If V4:V6 is occupied with entries, then,

V18:

=INDIRECT("R[-"&COUNTBLANK(V4:V17)+1&"]C",0)

will give the position of V6.

Eugenol

Answer 22

to get the last value from a column you can also use MAX function with IF function

=ARRAYFORMULA(INDIRECT("G"&MAX(IF(G:G<>"", ROW(G:G), )), 4)))

Totemism

Answer 23

I have gone through way too many of these implementations of last-row for a specific column. Many solutions work but are slow for large or multiple datasets. One of my use cases requires me to check the last row in specific columns across multiple spreadsheets. What I have found is that taking the whole column as a range and then iterating through it is too slow, and adding a few of these together makes the script sluggish.

My "hack" has been this formula:

=ROW(index(sheet!A2:A,max(row(sheet!A2:A)*(sheet!A2:A<>""))))-1

• Example: Add this to Cell A1, to find the last row in column A. Can be added anywhere, just make sure to manage the "-1" at the end depending on which row the formula is placed. You can also place this is another col, rather than the one you're trying to count, and you don't need to manage the -1. You could also count FROM a starting Row, like "C16:C" - will count values C16 onwards

• This formula is reliably giving me the last row, including blanks in the middle of the dataset

• To use this value in my GS code, I am simply reading the cell value from A1. I understand that Google is clear that spreadsheet functions like read/write are heavy (time-consuming), but this is much faster than column count last-row methods in my experience (for large datasets)

• To make this efficient, I am getting the last row in a col once, then saving it as a global variable and incrementing in my code to track which rows I should be updating. Reading the cell every-time your loop needs to make an update will be too inefficient. Read once, iterate the value, and the A1 cell formula (above) is "storing" the updated value for the next time your function runs

Please let me know if this was helpful to you! If I encounter any issues I will comment on this answer.

Orangeade

I think using helper cells is a great approach to save time by not re-computing expensive operations. – Mia

I'm curious about the performance of the formula itself. I wonder if using more built-in functions is more performant than arithmetic operations. For example, I wonder how this compares in terms of performance: =MATCH(1, ARRAYFORMULA(IF(ISBLANK(sheet!A2:A), #N/A, 0))) – Mia

My thoughts are that MAX requires checking every cell, whereas MATCH might be able to short-circuit on the assumption that its input range is sorted (which it will be if it's only zeroes, since #N/A errors are not considered during a match from what I've observed). I am assuming that ARRAYFORMULA evaluation is done lazily and that MATCH does some sort of binary search scan that tests the last entry of a row/column fairly early, but I don't know if these assumptions are true. – Mia

Answer 24

Original answer here

---

This problem can now be easily solved using the recently released TOCOL and CHOOSEROWS functions.

=CHOOSEROWS(TOCOL(A2:A,1),-1)

The TOCOL function with second argument set to 1 removes the blank values in the range A2:A and the CHOOSEROWS function with second argument set to -1 returns the first value from the bottom (i.e. the last value).

The same formula can also be used to return the last non-empty value from a row:

Note: In order for this formula to work, the empty values in the range must be actual empty values, not empty strings "". If the range contains empty strings, use the following formula instead:

=CHOOSEROWS(FILTER(A2:A,A2:A<>""),-1)

Dolph

Answer 25

=QUERY({G2:G9999,ARRAYFORMULA(ROW(G2:G9999))},"Select Col1 where Col1 is not null Order By Col2 desc limit 1",0)

In the query, Col1 refers to column G, and Col2 refers to a virtual column, populated with the row numbers returned by ARRAYFORMULA(ROW(G2:G9999)).

I haven't evaluated the other answers, so I can't say if this is the best way, but it worked for me.

Bonus: to return the first non-empty cell:
QUERY({G2:G9999},"Select Col1 where Col1 is not null limit 1",0)

Refs: QUERY, ARRAYFORMULA, ROW.

Dysart