+ Reply to Thread
Results 1 to 11 of 11

Gathering information by the most recent dates

  1. #1
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Gathering information by the most recent dates

    I have an employee summary sheet, and another sheet where data will be entered by date.

    On the summary sheet I want to gather only the last 3 dates that were entered.

    Attached is an example.

    thanks for any help.
    Nick
    Attached Files Attached Files
    Last edited by avidcat; 01-20-2010 at 10:18 AM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Gathering information by the most recent dates

    Hi,

    Just use
    Please Login or Register  to view this content.
    and change the last '1' to 2 & 3 for the 2nd and 3rd largest

    HTH

  3. #3
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering information by the most recent dates

    Thanks, that can work for the dates. Do you know how I can grab the other information next to the dates?

    thanks,
    Nick

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Gathering information by the most recent dates

    If the dates in Col A on Aces sheet are unique why not simply use VLOOKUP ?

  5. #5
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering information by the most recent dates

    Thanks DonkeyOte,

    I am not familiar with VLOOKUP, how is that formula written out?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Gathering information by the most recent dates

    Nick, check out XL Help files... post back if you run into difficulties.

  7. #7
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering information by the most recent dates

    Thanks DonkeyOte,

    I think I am on the right track, or maybe I am way off. Here is what I am working with but I do get a name error:

    =VLOOKUP(LARGE,ACES!A:A,1,TRUE)

    I am thinking maybe the value "LARGE" doesn't work in this case.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Gathering information by the most recent dates

    What is LARGE meant to represent ?

    Previously you were looking to retrieve data based on a Date value...

  9. #9
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering information by the most recent dates

    I left out part of what I pasted here:

    =VLOOKUP(LARGE(ACES!A:A,1),1,TRUE)

    I am trying to return the 3 most recent dates. I was seeing if I could use what Richard posted as a value in the VLOOKUP.

    But I am not having any luck, with the above I get a N/A.

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

    Re: Gathering information by the most recent dates

    I'm not sure you've followed the help files correctly regards VLOOKUP.

    First off as I see it you have your top 3 date issue resolved c/o Richard's formula.

    You need to now populate columns F, O & R based on the date value in B

    To do that - you can use VLOOKUP which, per the Help files and plethora of online examples, works along the lines of:

    =VLOOKUP(criteria,table_array,col_index_num[,range_lookup])

    where:

    The criteria is your date value in Column B

    The table_array is your ACES table - ie ACES!A5:D37 ...
    NOTE: the left most column of your range must contain the column in which the criteria will be resident.

    Your col_index_num will vary - this determines from which column in the table_array you wish to retrieve results ...
    for "Observed by" this will be 2 given this is the 2nd column in your table_array.

    The last parameter being optional... in your case given the table_array is unsorted you MUST use specify this final parameter as either 0 or FALSE - this is such than an exact match is conducted
    (the binary algorithm, utilise if this parameter is either omitted / 1 / TRUE can not be employed as it will return misleading results).


    So as a pointer your first formula... column F

    Please Login or Register  to view this content.
    For the other columns you need only modify the col_index_num - all other parameters are constant.

    You can if you wish look to utilise a MATCH function to create the col_index_num for you such that you do not have to modify it per column of calculations - I will leave this for you to investigate.

    All of this of course is assuming the dates in Column A on Aces sheet are unique... if not the above won't work should a top 3 date be duplicated...
    Last edited by DonkeyOte; 01-20-2010 at 09:55 AM.

  11. #11
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering information by the most recent dates

    Thanks, I thought I had to incorporate the LARGE value into the formula. I see that I just had to use that to gain a starting point and then use VLOOKUP based on that return.

    I appreciate your help, thanks for making me learn something today

    Nick

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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