Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Find last value in a column

    Dear Loungers,

    just can't get the formula right for this.

    I have a column with values in, they are numbers but in the form of text i.e. with a single quotation mark in front like this '0.01. I want to find the last value in the column - the column will have an increasing number of values over time but not more than 200 so the range will be e.g. C4:C200. the data could look like this:
    C4 = '0.01
    C5 = '0.02
    C6 = '1.0
    C7 = blank

    So in this case I want to return '1.00

    Can anyone help?

    Thank you............................ liz

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Lizat,

    I know you are looking for a formula but a quick resolution could be a macro. Change the 3 in both lines to the column number you wish to search for.

    HTH,
    Maud

    Code:
    Public Sub GetLastNum()
        LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'CHANGE THE 3 TO THE COLUMN WITH VALUES
        MsgBox Cells(LastRow, 3).Value  'CHANGE THE 3 TO THE COLUMN WITH VALUES
    End Sub
    Last edited by RetiredGeek; 2013-04-03 at 09:45.

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thank you.

    The reason i wanted a formula is that i need to do it several times and display the result in a cell at the top of the column in question. I am trying it with index and match but never quite do it right so I am hoping someone will help - likely to be RetiredGeek who is one of the most generous members here

    liz

  4. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    This seems to work:

    =INDEX(E4:E200,COUNTA(E4:E200),1)

    It doesn't cope if the sequence has cells with blank in them but is good enough

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Liz,

    Thanks for the kind words. I guess I'll have to come up with an answer.

    Actually, I think Maud was on the right track but instead of a Sub use a function.
    Code:
    Option Explicit
    
    Public Function GetLastNum(lCol As Long) As Double
        Application.Volatile
        GetLastNum = ActiveSheet.Cells(Rows.Count, lCol).End(xlUp).Value
    End Function
    Volatile.JPG
    The advantages of using this function vs formula:
    1. Easier to type so less prone to input errors.
    2. Is not affected by blank rows
    3. Not limited to the number of rows so you don't have to worry about going over the limit and forgetting to change the formula


    Syntax:
    =GetLastNum(Column Number)

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    lizat (2013-04-03)

  7. #6
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    fab!!!!

  8. #7
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi lizat

    Try the LOOKUP function:
    =LOOKUP(2,1/(C:C<>""),C:C)

    Kevin

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Kevin: can't get yours to work.
    RG: nice function, but if you use your function in column D (as per your example), then you must always make sure there's at least one value below it in the same column! (or you'll get the circular error message).
    And to use it for returning values from say column PKQ (what number is that???) I would use your function like this:
    =GetLastNum(Column(PKQ1))
    ..by clicking any cell in the required column e.g. cell PKQ1

    Now, for those of you who are not allowed to have macro-enabled files (and so cannot use custom functions), you are stuck with something like the following, which will return the last value in a specified column, whether the last value is numeric or text and whether the column includes blanks or not:
    =INDEX(A:A,MAX(MATCH(9.99E+307,A:A),MATCH(REPT("z" ,255),A:A)))

    ..using column [A] as the example.
    The formula will return #N/A if the specified column is completely empty.

    zeddy

  10. #9
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi zeddy

    See the attached file.

    Kevin
    Attached Files Attached Files

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    =GetLastNum(Column(PKQ1))
    Love it! Thanks.

    =INDEX(A:A,MAX(MATCH(9.99E+307,A:A),MATCH(REPT("z" ,255),A:A)))
    Proves my point 1.

    Ok, I added the error check so it returns zero if there are no other rows.
    Code:
    Option Explicit
    
    Public Function GetLastNum(lCol As Long) As Double
    
        Dim lActiveRows As Long
        Application.Volatile
        
        lActiveRows = _
           Cells(Rows.Count, lCol).End(xlUp).Row
        
        If lActiveRows > 1 Then
          GetLastNum = ActiveSheet.Cells(Rows.Count, lCol).End(xlUp).Value
        Else
          GetLastNum = 0
        End If
        
    End Function
    Volatile.JPG
    It will even work if it's not in row 1!
    Last edited by RetiredGeek; 2013-04-03 at 13:23.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    Your point is proved of course.
    I agree with you.
    I like your solution best.
    If you are allowed to have a macro-enabled file that is.

    I guess you spotted that my formula combines finding the row of the last text value, and the row of the last numeric value, and then just returning the last entry.
    Of course you could simplify the formula if you only wanted the last number. Or if you only wanted the last text entry.


    zeddy

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    RG,

    Yes, a function instead of a sub is definitely the way to go here. Application.Volatile is command I should use more often in my functions. Thanks for the reminder.

    Maud

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Kevin

    I now like your formula very much.
    But check out my attached files.
    It didn't work in one file, but did in the other!
    Go figure!!

    zeddy
    Attached Files Attached Files

  15. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If all the numbers are stored as text, then
    =LOOKUP("A",C2:C400)
    should work
    Regards,
    Rory

    Microsoft MVP - Excel

  16. #15
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi zeddy

    Strange that, as both files work for me!

    Kevin


    Quote Originally Posted by zeddy View Post
    Hi Kevin

    I now like your formula very much.
    But check out my attached files.
    It didn't work in one file, but did in the other!
    Go figure!!

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •