Page 1 of 3 123 LastLast
Results 1 to 10 of 25

last item in a column

This is a discussion on last item in a column within the Excel Questions forums, part of the Question Forums category; Hi there, it's me again, Excel n00b. Just wondering if there's a formula for retrieving the last item in a ...

  1. #1
    New Member
    Join Date
    Apr 2004
    Posts
    10

    Default last item in a column

    Hi there, it's me again, Excel n00b.

    Just wondering if there's a formula for retrieving the last item in a column and then storing that value into another cell?


  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,485

    Default

    text or numerical?
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,253

    Default Re: last item in a column

    Quote Originally Posted by Caius
    Hi there, it's me again, Excel n00b.

    Just wondering if there's a formula for retrieving the last item in a column and then storing that value into another cell?

    Is it text or numeric?

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,485

    Default

    an example...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book5___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    5last*numberlast*text*
    2
    42a*
    3
    6***
    4
    pop***
    5
    lol***
    6
    7***
    7
    4***
    8
    2***
    9
    a***
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    text formula is:

    =VLOOKUP(REPT("z",255),A:A,1)

    numerical formula is:

    =VLOOKUP(9.99999999E+307,A:A,1)
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  5. #5
    New Member
    Join Date
    Apr 2004
    Posts
    10

    Default Re: last item in a column

    Hot **** you guys are fast. I sure hope you're getting paid to answer these questions. If you're not, you should be!


  6. #6
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,485

    Default Re: last item in a column

    Quote Originally Posted by Brian from Maui
    Quote Originally Posted by Caius
    Hi there, it's me again, Excel n00b.

    Just wondering if there's a formula for retrieving the last item in a column and then storing that value into another cell?

    Is it text or numeric?
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  7. #7
    New Member
    Join Date
    Apr 2004
    Posts
    10

    Default Re: last item in a column

    Oh, and to answer your question it was text.

    thanks again!

  8. #8
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,485

    Default

    you're very welcome!
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  9. #9
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,253

    Default Re: last item in a column

    Quote Originally Posted by Caius
    Oh, and to answer your question it was text.

    thanks again!
    =INDEX(G:G,MATCH(REPT("z",255),!G:G))

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

    Using the Morefunc addin

    =LASTROW(G:G)

  10. #10
    Board Regular
    Join Date
    Nov 2003
    Posts
    50

    Default Re: last item in a column

    Using firefytr's formula is there a way to return the second to last value? I'm specifically interested in the numeric version.

    Ultimate goal: Return the last value, the second to last value, and the change between the two.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com