+ Reply to Thread
Results 1 to 14 of 14

Find SECOND last active cell in column

  1. #1
    Registered User
    Join Date
    08-26-2009
    Location
    Lima, Peru
    MS-Off Ver
    Excel 2007
    Posts
    37

    Question Find SECOND last active cell in column

    Hi,

    I use the Index/Match formula to find the last active cell in a column quite effectively.

    I'm wondering though how to adapt it to find the second last active cell?

    EX: Last active cell formula:
    Data:
    a 10
    b 11
    c 0
    d 12
    Please Login or Register  to view this content.
    Is it possible to use INDEX/MATCH to return the second most recent active cell, in this case, B2?

    Thanks!

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Find SECOND last active cell in column

    Are your numbers in ascending order like you write in example?

    If yes =LARGE(B:B;2) would be easiest

  3. #3
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Find SECOND last active cell in column

    Hi Mworonuk,

    You should just be able to add a -1 to the end of the match function, e.g.

    =INDEX(B$1:B$4,MATCH(9.99999999999999E+307,B$1:B$4)-1)

    Of course, if the cell above the last value is blank, this will return 0, not the second to last actual value.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Find SECOND last active cell in column

    Quote Originally Posted by zbor View Post
    Are your numbers in ascending order like you write in example?
    10, 11, 0, 12 are in ascending order??? I have been out of the math loop for a long time, but really?


  5. #5
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Find SECOND last active cell in column

    If the numbers are either 0 or incremented by 1 over the last non-zero number, you can use =INDEX(B1:B4,MATCH(MAX(B1:B4)-1,B1:B4,0)) or just =INDEX(B:B,MATCH(MAX(B:B)-1,B:B,0)) if you don't have anything else in that column. This will also work with blanks in the data.
    Last edited by darkyam; 01-15-2010 at 06:08 PM.

  6. #6
    Registered User
    Join Date
    08-26-2009
    Location
    Lima, Peru
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Find SECOND last active cell in column

    I apologize, I doubted my own abilities!

    For any one else looking for this type of formula here it is.
    Please Login or Register  to view this content.
    Note:
    1. To use in a different column you must also manually change the INDIRECT text: "A1:A" to whatever you like
    2. This will return 0, but will not return any nil values (which is what I wanted) To eliminate the 0... well that's more complicated.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Find SECOND last active cell in column

    Quote Originally Posted by Paul View Post
    10, 11, 0, 12 are in ascending order??? I have been out of the math loop for a long time, but really?

    Well, since 0 is not a number, I don't see mistake in the question :p

  8. #8
    Registered User
    Join Date
    08-26-2009
    Location
    Lima, Peru
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Find SECOND last active cell in column

    Wow you guys responded faster than I imagined!

    Actually the numbers could be anything in no particular order. The case is though that it would either be a positive or nill.

    Case:
    Reports are received for widgets every month (each row), some months are bigger than others, some months aren't reported at all.

    Therefore the numbers could be:
    Data:
    a 99
    b 105
    c
    d 23
    We're never interested in non-reported months.

    What I posted seems to work, but its rather complicated because I don't fully understand the match function...

  9. #9
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Find SECOND last active cell in column

    You can simplify yours considerably by using
    Please Login or Register  to view this content.
    As with yours, it will return the 0.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Find SECOND last active cell in column

    Here, try this:

    =LOOKUP(99^99;INDIRECT("B1:"&ADDRESS(MATCH(99^99;B:B)-1;2)))

    (replace ; with , if needed)

    =LOOKUP(99^99,INDIRECT("B1:"&ADDRESS(MATCH(99^99,B:B)-1,2)))

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Find SECOND last active cell in column

    Non-Volatile version of zbor's approach which replaces INDIRECT with darkyam's INDEX approach - again assumes non-report months are non-numeric

    Please Login or Register  to view this content.
    To handle instances of only one number you would need to add a handler - pending version you could use IFERROR (XL2007 and beyond) or use a COUNT test

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-08-2014
    Location
    Orlando, Florida
    MS-Off Ver
    2010
    Posts
    10

    Re: Find SECOND last active cell in column

    Piggybacking on this OLD thread... From the original question, how would you select the cell that was the second to the last active cell?

    I have some VBA code that fills in the active cell (E4) based on double-clicking another cell, but I want excel to select the cell to the right (F4) of the second to the last active cell (the one I just populated 'E4').

    Any help is appreciated.

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Find SECOND last active cell in column

    @tpele1
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
    Ben Van Johnson

  14. #14
    Registered User
    Join Date
    08-08-2014
    Location
    Orlando, Florida
    MS-Off Ver
    2010
    Posts
    10

    Re: Find SECOND last active cell in column

    Oops... My bad. Apologies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1