Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Finding recent date from list

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Finding recent date from list

    This is my first post so I apologize if my request is easier than I expected. I have been trying to figure this out for a while. I have two columns of data. In column one is simply dates, in column two is the person attending on that date. There are various people who attend on various dates. What I am trying to find is the last time each person attended. Ive used index, max, and all kinds of formulas with no luck.

    column 1 Column 2
    08-Apr-15 Dan
    09-Apr-15 Bill
    10-Apr-15 Dan
    11-Apr-15 Phil
    12-Apr-15 Tom
    13-Apr-15 Dan
    14-Apr-15 Dustin
    15-Apr-15 Dan
    16-Apr-15 Phil
    17-Apr-15 Bill
    18-Apr-15 Dan

    With this, when was the last time Dan attended, Bill, Phil and so on. If you can help, I would appreciate it so much.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Finding recent date from list

    Row\Col
    A
    B
    C
    1
    date name last attendance date
    2
    8-Apr-15
    Dan
    18-Apr-15
    3
    9-Apr-15
    Bill
    17-Apr-15
    4
    10-Apr-15
    Dan
    18-Apr-15
    5
    11-Apr-15
    Phil
    16-Apr-15
    6
    12-Apr-15
    Tom
    12-Apr-15
    7
    13-Apr-15
    Dan
    18-Apr-15
    8
    14-Apr-15
    Dustin
    14-Apr-15
    9
    15-Apr-15
    Dan
    18-Apr-15
    10
    16-Apr-15
    Phil
    16-Apr-15
    11
    17-Apr-15
    Bill
    17-Apr-15
    12
    18-Apr-15
    Dan
    18-Apr-15
    13


    Assuming that dates associated with each name is sequential (entered from earliest to latest)...

    C2, copied down:

    =LOOKUP(9.99999999999999E+307,1/($B$2:$B$12=$B2),$A$2:$A$12)
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding recent date from list

    That is great. Hours of stress solved in a flash. Now if this data is on a separate sheet, how do I bring that forward. The formula that you have works if the data is on that page. Mine is on a separate page (ie sheet 2). Thanks again.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Finding recent date from list

    Quote Originally Posted by jimmy604 View Post
    That is great. Hours of stress solved in a flash. Now if this data is on a separate sheet, how do I bring that forward. The formula that you have works if the data is on that page. Mine is on a separate page (ie sheet 2). Thanks again.
    Just add the sheet prefix to the appropriate ranges...

    =LOOKUP(9.99999999999999E+307,1/(Sheet2!$B$2:$B$12=$B2),Sheet2!$A$2:$A$12)

    Note. If you really meant "sheet 2", I'd suggest to revert back to the default name Sheet2.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    May 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding recent date from list

    When I add the sheet prefix, I get a 00/01/1900. I use the date format to no avail.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Finding recent date from list

    Quote Originally Posted by jimmy604 View Post
    When I add the sheet prefix, I get a 00/01/1900. I use the date format to no avail.
    Let's see how the formula looks after what you did to it...
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    May 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding recent date from list

    Looking at my post I think where the problem lies is that I am looking for a summary. Using my initial post

    column 1 Column 2
    08-Apr-15 Dan
    09-Apr-15 Bill
    10-Apr-15 Dan
    11-Apr-15 Phil
    12-Apr-15 Tom
    13-Apr-15 Dan
    14-Apr-15 Dustin
    15-Apr-15 Dan
    16-Apr-15 Phil
    17-Apr-15 Bill
    18-Apr-15 Dan

    I need to make a second table that obtains the last time each attended. Extracting the data from sheet2

    Name Date
    Dan
    Bill
    Phil
    tom
    Dustin

    this might give a better sense of the goal.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Finding recent date from list

    Quote Originally Posted by jimmy604 View Post
    Looking at my post I think where the problem lies is that I am looking for a summary. Using my initial post

    column 1 Column 2
    08-Apr-15 Dan
    09-Apr-15 Bill
    10-Apr-15 Dan
    11-Apr-15 Phil
    12-Apr-15 Tom
    13-Apr-15 Dan
    14-Apr-15 Dustin
    15-Apr-15 Dan
    16-Apr-15 Phil
    17-Apr-15 Bill
    18-Apr-15 Dan

    I need to make a second table that obtains the last time each attended. Extracting the data from sheet2

    Name Date
    Dan
    Bill
    Phil
    tom
    Dustin

    this might give a better sense of the goal.
    Let's Sheet3 house the output table, with names in column A from A2 on.

    In B2 enter and copy down:

    =LOOKUP(9.99999999999999E+307,1/(Sheet2!$B$2:$B$12=$A2),Sheet2!$A$2:$A$12)
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    May 2015
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding recent date from list

    I'm probably doing something wrong here. When I do the formula, I get the 00/01/1900 in the solution. here is the formula

    =LOOKUP(9.99999999999999E+307,1/(Logbook!$A$6:$A$35000=$A6),Logbook!$B$6:$B$3500)

    Looking at the worksheet, it appears that the formula is highlighting a6 in my current sheet rather than looking to logbook. The data that I want to extract is in logbook. Column A is the date, and column b is the person. I'm trying to extract this data into another sheet to show the most recent time someone attended.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Finding recent date from list

    Quote Originally Posted by jimmy604 View Post
    I'm probably doing something wrong here. When I do the formula, I get the 00/01/1900 in the solution. here is the formula

    =LOOKUP(9.99999999999999E+307,1/(Logbook!$A$6:$A$35000=$A6),Logbook!$B$6:$B$3500)

    Looking at the worksheet, it appears that the formula is highlighting a6 in my current sheet rather than looking to logbook. The data that I want to extract is in logbook. Column A is the date, and column b is the person. I'm trying to extract this data into another sheet to show the most recent time someone attended.
    Where did you enter this formula, that is, in which sheet and in which cell of that sheet?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

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
  •