Find Last value in column *regardless* of number, text, or number-as-text?

[jombi]

Hi all,
I have searched far and wide, and can't find a good formula (not VBA code) to accomplish this.
I've used the below, for example in Column E, the actual data starts in row 4, so I can't use the full E:E syntax:

=IF(ISERROR(INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000))),INDEX(Data!E$4:E$65000,MATCH("z",Data!E$4:E$65000)),INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000)))

The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)

However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.

Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
Thank you,
Jombi

 

[Rick Rothstein]

Does this do what you want?

=LOOKUP(2,1/(E:E<>""),E:E)

Although it would be more efficient if you could limit the range of the first address to cover only the cells you could plausibly have data in, something like this...

=LOOKUP(2,1/(E1:E1000<>""),E:E)

 

[jombi]

Rick,
Thanks very much, it worked!

Sort of...I limited the range in the first address, but found I had to also limit the second address to the same range, is that correct? I mean, it works with both ranges equally limited, but I just want to make sure that by doing that, I'm not affecting some future situation that I haven't come across yet.
Thanks very much,
--Jombi

 

[Rick Rothstein]

I limited the range in the first address

Why? Unless you expect there to be no data after E3, the starting row is immaterial.

I limited the range in the first address, but found I had to also limit the second address to the same range, is that correct?

The second range can be bigger than the first (it is where the result is being drawn from, so it doesn't matter if there are more cells than the first as they will never be looked at; HOWEVER, both ranges must start from the same row (if middle argument calculates an offset from its ranges first cell, so that offset (used to look in the third argument's range must start from the same row).

 

[jombi]

Rick,
Thanks for clarifying that. What I meant by 'first address' was the address in the denominator in the second argument, just as your example, E1:E1000 (as opposed to E:E). So I was thinking I should make the third arg the same.
It's working great--a very elegant solution compared to what I'd had.

Perhaps this is an issue for another topic, but when I open the sheet, I need to select the cell with this formula and do the F2-Enter thing. All of my searching on that talks about formatting, etc. But one example column is explicitly formatted as a number, and digits are the only value that ever exist--it's just that sometimes, seemingly randomly, excel decides that 5 is actually "5" (no quotes, no leading tick, but it puts the green corner number-as-text thingy in) and other times it correctly treats it as a number.
-jombi

 

[Rick Rothstein]

Perhaps this is an issue for another topic, but when I open the sheet, I need to select the cell with this formula and do the F2-Enter thing. All of my searching on that talks about formatting, etc. But one example column is explicitly formatted as a number, and digits are the only value that ever exist--it's just that sometimes, seemingly randomly, excel decides that 5 is actually "5" (no quotes, no leading tick, but it puts the green corner number-as-text thingy in) and other times it correctly treats it as a number.

Are you sure the only thing in the cell is the number? Could there perhaps be a leading or trailing non-visible character such as a space (ASCII 32) or non-breaking space (ASCII 160)?

 

[Aladin Akyurek]

What is your version of Excel?

 

[jombi]

I'm using 2007, but it's a 2003 .xls sheet in compatibility mode, this because some of our users are still on Excel 2003.

 

[Aladin Akyurek]

Hi all,
I have searched far and wide, and can't find a good formula (not VBA code) to accomplish this.
I've used the below, for example in Column E, the actual data starts in row 4, so I can't use the full E:E syntax:

=IF(ISERROR(INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000))),INDEX(Data!E$4:E$65000,MATCH("z",Data!E$4:E$65000)),INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000)))

The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)

However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.

Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
Thank you,
Jombi

I'm using 2007, but it's a 2003 .xls sheet in compatibility mode, this because some of our users are still on Excel 2003.

Define BigNum by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=9.99999999999999E+307

BigStr as referring to:

=REPT("z",255)

and DataRef as referring to:

=Data!$E$4:INDEX(Data!$E:$E,ROWS(Data!$E:$E))

For any last value, even an error or a formula blank, (i.e. "")...

=LOOKUP(BigNum,1/(1-ISBLANK(DataRef)),DataRef)

For the last value that is either text or number...

1)

=LOOKUP(BigNum,1/(ISTEXT(DataRef)+ISNUMBER(DataRef)),DataRef)

2)

=INDEX(Data!E:E,MIN(ROW(DataRef)-1)+
  LOOKUP(BigNum,CHOOSE({1,2,3},MATCH(BigNum,DataRef),MATCH(BigStr,DataRef),
    MAX(MATCH(BigNum,DataRef),MATCH(BigStr,DataRef)))))

Option 2 is faster that Option 1.

 

[jombi]

Aladin,
Thank you for those suggestions. I've been off a few days so haven't had the chance to try them. They look promising as well!
Jombi