+ Reply to Thread
Results 1 to 4 of 4

Finding last instance and second last instance in a list

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    London
    MS-Off Ver
    Mac Office
    Posts
    45

    Finding last instance and second last instance in a list

    Hi,

    I have attached an example sheet. Basically I have an ordered list (dates in column A, people's names in column B, scores in column C). In column D I want to find the last score achieved by the person in that row (e.g. Tom in row 6) and I have achieved that using the following formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You can see it correctly returns 3.5. But what I'd then like to do is to find the second last instance of the name (i.e. two back) so in this case it should be 2. Is there any way to do this with modification of the above formula? Ideally if possible I'd like to avoid array formulas or overly complex formulas as I'll need to run this on a sheet with around 100,000 lines so want the most clean and efficient option for calculation times.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Finding last instance and second last instance in a list

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But I think that maybe this solution will work better for you.

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    By changing value (in red) : 1 will return 2, 2 will return 3.5

    Or there is also a shorter version for array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 05-07-2016 at 11:27 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    03-06-2015
    Location
    London
    MS-Off Ver
    Mac Office
    Posts
    45

    Re: Finding last instance and second last instance in a list

    Thanks your array solution works well. However, it seems that it counts from the top (number in red), but is there a way to get it to count backwards from the current row. E.g. there may be 2000 instances of "Tom" in the list and I'll want the readings from 1998 and 1999 instances, but I won't know how many there are (i guess unless I did a countif or something)?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Finding last instance and second last instance in a list

    I assume that 1998-1999 are years.
    I made some changes to the formula to reflect year range. The formula will start from the bottom.
    Also, if need to count all instances of the name in the last row of the column B you can use this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter this array formula in D2 and drag it to E2
    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E
    1 Date Name Score Last Second Last
    2 1/1/2016 Alex 1 3.5 2
    3 1/1/2016 Dan 4
    4 1/1/2016 Tom 2
    5 1/2/2016 Liam 5
    6 1/2/2016 Len 6
    7 1/3/2016 Luke 7
    8 1/3/2016 Alex 8
    9 1/3/2016 Steve 9
    10 1/3/2016 Tom 3.5
    11 1/4/2016 Len 5
    12 1/4/2016 Dave 3
    13 1/4/2016 Steve 1
    14 1/5/2016 Alex 5
    15 1/5/2016 Tom 6

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 07-15-2014, 09:50 AM
  2. finding the nth instance in a formula
    By ammartino44 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2014, 05:12 AM
  3. Finding Last instance in a list
    By skate1991 in forum Excel General
    Replies: 3
    Last Post: 05-09-2013, 11:51 AM
  4. Finding first instance of a value
    By goldenclick in forum Excel General
    Replies: 3
    Last Post: 06-06-2011, 03:14 PM
  5. Finding only 1 instance not multiple
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-27-2010, 01:36 AM
  6. Finding first instance in an array
    By rdouglas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2008, 10:59 AM
  7. Finding the 2nd instance in a lookup
    By tuph in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2007, 11:38 PM

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