last item in a column

[Harvey Waxman]

Is there a function that will return the last non-blank cell at the end of a
column range?

 

[Domenic]

Is there a function that will return the last non-blank cell at the end of a
column range?

Try the following...

For numerical data:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

For text data:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

For a range containing both text and numerical data:

=INDEX(A:A,LOOKUP(2,1/(A1:A100<>""),ROW(A1:A100)))

Hope this helps!

 

[Harvey Waxman]

Try the following...

For numerical data:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

For text data:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

For a range containing both text and numerical data:

=INDEX(A:A,LOOKUP(2,1/(A1:A100<>""),ROW(A1:A100)))

Hope this helps!

It works perfectly. Now, if I could understand exactly what it means...

Thanks

 

[Domenic]

It works perfectly. Now, if I could understand exactly what it means...

Thanks

Actually, I would use the following formulas instead...

For numerical data:

=LOOKUP(9.99999999999999E+307,A:A)

For text data:

=LOOKUP(REPT("z",255),A:A)

For a range containing both text and numerical data:

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

Hope this helps!

P.S. I'm just turning in for the night .... it's past my bedtime
..... so if nobody jumps in I'll try and explain it tomorrow.

 

[Harvey Waxman]

Try the following...

For numerical data:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

snip

Puzzled. This works as advertised in a new spreadsheet and one existing
spreadsheet.

When I copy the formula into an different existing spreadsheet the result
displayed is not the last item in the column.

I can't see what could make the difference. In the offending sheet the
displayed number was 149.00 but the actual value was 2188. The cells were
formatted as general. I even coerced the target column by special
pasting/multiplying by 1 just to be certain and when I did this the displayed
number changed from 149 to 0!

Ideas?

 

[Harvey Waxman]

Actually, I would use the following formulas instead...

For numerical data:

=LOOKUP(9.99999999999999E+307,A:A)

For text data:

=LOOKUP(REPT("z",255),A:A)

For a range containing both text and numerical data:

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

Hope this helps!

P.S. I'm just turning in for the night .... it's past my bedtime
.... so if nobody jumps in I'll try and explain it tomorrow.

Forget my puzzle. I found the problem but am too ashamed to tell anyone.

I still would appreciate the explanations though.

Thanks again

 

[Domenic]

Basically, here is how the formulas work...

[1] =LOOKUP(9.99999999999999E+307,A:A)

9.99999999999999E+307 is the largest numerical value recognized by Excel
and, therefore, LOOKUP returns the last numerical value in Column A.

[2] =LOOKUP(REPT("z",255),A:A)

REPT("z",255) is the largest text value recognized by Excel and, again,
LOOKUP returns the last text value in Column A.

[3) =LOOKUP(2,1/(A1:A4<>""),A1:A4)

Let's assume that A1:A4 contains the following values...

{10;"";15;"red"}

The conditional statement...

(A1:A4<>"")

....will return the following array of values...

{TRUE;FALSE;TRUE;TRUE}

Now, whenever you have a conditional statement involved in some sort of
mathematical operation, TRUE/FALSE's are coerced into their numerical
equivalent of 1/0's.

So if we look at...

1/(A1:A4<>"")

....the following array of values is returned...

{1;#DIV/O!;1;1}

Note that 1 will always be the largest number in the array and that
LOOKUP ignores #DIV/O! errors in its evaluation.

Therefore, using the number 2 as the lookup value will find the last
value in this array (lookup vector ---> {1;#DIV/O!;1;1}) and return the
corresponding value in the range (result vector ---> {10;"";15;"red"}).

Hope this helps!

 

[JE McGimpsey]

[2] =LOOKUP(REPT("z",255),A:A)

REPT("z",255) is the largest text value recognized by Excel and, again,
LOOKUP returns the last text value in Column A.

minor niggle:

REPT("z",255) *is* a large text value starting with the 26 English
letters.

However, ANY string longer than 255 characters will be "larger"... For
instance, put

=REPT("A",256)

below other cells, and the last text cell before the "aaa..." will be
returned.

In most practical applications, that's not an issue, but if you have a
short paragraph (but longer than 255 characters), you'll get a bogus
answer.

This isn't really fixable using LOOKUP - while REPT() can produce a
string up to 32767 characters, the LOOKUP function can only take a
lookup value of 255 characters or less.

You can always use something like this UDF, regardless of the type of
data:

Public Function LastValue(ByVal rng As Range) As Variant
Dim vTemp As Variant
With rng.Resize(, 1)
If Application.CountA(.Cells) = 0 Then
vTemp = CVErr(xlErrNA)
ElseIf IsEmpty(.Cells(.Count).Value) Then
vTemp = .Cells(.Count).End(xlUp).Value
Else
vTemp = .Cells(.Count).Value
End If
End With
LastValue = vTemp
End Function


call as

=LastValue(A:A)

or

=LastValue(A10:A40)

 

[Harvey Waxman]

Basically, here is how the formulas work...

Thanks to both of you. Much appreciated.

 

[Domenic]

A minor niggle, maybe...but definitely an important distinction. That's
something I hadn't realized. So thanks for pointing that out.

Also, I like that UDF. That's a keeper.

Thanks again, JE! Much appreciated!

[2] =LOOKUP(REPT("z",255),A:A)

REPT("z",255) is the largest text value recognized by Excel and, again,
LOOKUP returns the last text value in Column A.

minor niggle:

REPT("z",255) *is* a large text value starting with the 26 English
letters.

However, ANY string longer than 255 characters will be "larger"... For
instance, put

=REPT("A",256)

below other cells, and the last text cell before the "aaa..." will be
returned.

In most practical applications, that's not an issue, but if you have a
short paragraph (but longer than 255 characters), you'll get a bogus
answer.

This isn't really fixable using LOOKUP - while REPT() can produce a
string up to 32767 characters, the LOOKUP function can only take a
lookup value of 255 characters or less.

You can always use something like this UDF, regardless of the type of
data:

Public Function LastValue(ByVal rng As Range) As Variant
Dim vTemp As Variant
With rng.Resize(, 1)
If Application.CountA(.Cells) = 0 Then
vTemp = CVErr(xlErrNA)
ElseIf IsEmpty(.Cells(.Count).Value) Then
vTemp = .Cells(.Count).End(xlUp).Value
Else
vTemp = .Cells(.Count).Value
End If
End With
LastValue = vTemp
End Function


call as

=LastValue(A:A)

or

=LastValue(A10:A40)