+ Reply to Thread
Results 1 to 13 of 13

Pulling nth date from large dataset

  1. #1
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Pulling nth date from large dataset

    Hi,

    I manage grants - we use # of days per disbursement as a performance measure to ensure that we are providing out grantees with the appropriate amount of service. I keep a tracking chart that I manage with overseas partners that use these dates to help them prioritize the 30+ grantees in their portfolio at any given time. It would be great if this # of days to disbursement #1, disbursement #2, etc could automatically pull to show them who they have neglected.

    "Sheet 1" = Overview sheet to see general information (where I'm trying to pull to)
    "Sheet 2" = table to track information as the disbursements or other actions are processed per grant
    Column A (on both sheets) gives the grant reference
    Column B (data entry sheet) gives the date the payment was sent

    When I do =SMALL(('Sheet2'!B:B),2), I get the 2nd smallest in the whole sheet, but then when I try to make an IF function to tie it to the specific grant...

    =IF('Sheet2'!A:A,A2,SMALL(('Sheet2'!B:B),2)) --> this gives me a 1905 date

    I've tried a bunch of different formulas and tried reformatting the dates... but I'm having very little success...

    Please help!
    Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,905

    Re: Pulling nth date from large dataset

    your IF() statement seems incomplete?

    =IF('Sheet2'!A:A,A2,SMALL(('Sheet2'!B:B),2))

    =IF('Sheet2'!A:A....is what?

    the IF() syntax is...
    =if(criteria-to-test,what-to-do-if-test=TRUE,what-to-do-if-test=FALSE)

    Based on that, you have your criteria, but are not testing it?

    Also, if you are using a range like that, it is probably an array formula and needs to be entered with CTRL SHIFT enter, not just enter


    It's hard to offer suggestion without seeing any data though
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    Thanks for trying - I keep seeing this CTRL SHIFT ENTER - I don't understand this and need to maybe try that...

    I guess the formula is:

    =IF (pull from "grant column" on data entry page, "grant #" reference on cell entry page, SMALL("date list" on data entry page, 2 for 2nd smallest))

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,905

    Re: Pulling nth date from large dataset

    =IF (pull from "grant column" on data entry page, "grant #" reference on cell entry page, SMALL("date list" on data entry page, 2 for 2nd smallest))

    pull from "grant column" on data entry page....and do what with it??? That's my point, you are specifying a range (which would normally be a single cell), but once you have it, you are not doing anything with it.

    =if(criteria-to-test,what-to-do-if-test=TRUE,what-to-do-if-test=FALSE)
    =if(A5<10, do this, else do that)
    =if("green"="blue", do this, else do that)
    =if("this"<>"that", do this, else do that)

    Do you see what Im getting at? you have to have a test of some sort for the 1st part of an if() statement - it needs to have a TRUE or FALSE to base its decision on, you dont have that.

    Hmm maybe you just need to do something like this...

    =iferror(SMALL(('Sheet2'!B:B),row(a1)),"")

    This, copied down, will pull out the smallest (1) value from B, then in the next row will pull out the 2nd smallest (2) value etc
    Last edited by FDibbins; 03-29-2013 at 12:48 AM.

  5. #5
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    Ok... I think I'm understanding...

    I have tried:

    If(pull from "grant column" = "grant #", small("date list", 2),0) - gives me 1900
    If(pull from "grant column" = "grant #", small("date list", 2),"") - gives me blank
    If(pull from "grant column" <> "grant #", small("date list", 2),"") - gives me the 2nd date for the whole dataset

    Thank you for your help on this!

  6. #6
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    IFERROR only seems to pull up the first date...

  7. #7
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    I just figured out how to share this... https://docs.google.com/file/d/0Bwp6...it?usp=sharing
    Last edited by guambear; 03-29-2013 at 01:31 AM.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,905

    Re: Pulling nth date from large dataset

    HI. Im not sure what the purpose was of sharing that file?

    If it was an attempt to give us the data you are working with, we cant work with pictures, and no-one wants to retype your data.

    If it was to show us a solution, it doesnt show.

    I would suggest, that if you want to send us a file, you send it through the forum.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  9. #9
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    Hi,

    Sorry I thought using google docs was easy for file sharing. I didn't see where I could attach a document in the forum - although I saw that other people had done so. I left the office at close to 2am last night, so I was a bit tired and not very coherent.

    Thanks for letting me know how to upload... and all of your help! It's greatly appreciated.
    Attached Files Attached Files

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,905

    Re: Pulling nth date from large dataset

    Sorry, didnt mean to sound snippity Its just that not every=one can access file-hosting sites like googledocs etc because of company firewalls and stuff

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Pulling nth date from large dataset

    second smallest dependent on grant number pulled from bfa requests

    =SMALL(IF('BFA Requests'!$A$1:$A$1000=A2,'BFA Requests'!$C$1:$C$1000,""),2) ARRAY ENTERED is that what you mean?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  12. #12
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    You SOLVED IT!!!!!!! Thank you... that was driving me bonkers!
    Last edited by guambear; 03-29-2013 at 05:03 PM.

  13. #13
    Registered User
    Join Date
    03-29-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pulling nth date from large dataset

    No worries - I do my best to navigate, but considered myself often times very uninformed in terms of proper internet etiquette.

+ 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