last item in a column

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: last item in a column

  1. #1
    New Member
    Join Date
    Apr 2004
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    text or numerical?
    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (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,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  5. #5
    New Member
    Join Date
    Apr 2004
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  7. #7
    New Member
    Join Date
    Apr 2004
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you're very welcome!
    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (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,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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