+ Reply to Thread
Results 1 to 3 of 3

Pull next date in unsorted data given criteria

  1. #1
    Registered User
    Join Date
    10-03-2014
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    15

    Pull next date in unsorted data given criteria

    Hi all,

    I am trying to find the next closest date in a set of unsorted data. If the ID in column AD matches, and the name in column J matches, I want to return the next date in the array to the cell in column I. This formula would be in column V, which I’ve filled out with the correct result I want pulled.

    I hope this makes sense, I just want to pull the next date given two criteria.

    Column I Column J Column V Column AD
    1/23/2013 Amp 12/25/2013 2A
    9/23/2013 Ben 12/26/2013 3A
    8/2/2013 Ben 11/5/2013 2A
    12/25/2013 Amp Blank 2A
    12/26/2013 Ben Blank 3A
    11/5/2013 Ben Blank 2A
    1/2/2013 Amp 1/23/2013 2A

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Pull next date in unsorted data given criteria

    V2:

    =IFERROR(SMALL(($AD$2:$AD$8=AD2)*($J$2:$J$8=J2)*($I$2:$I$8>I2)*($I$2:$I$8),
    COUNT($I$2:$I$8)-MIN(1,SUMPRODUCT(($AD$2:$AD$8=AD2)*($J$2:$J$8=J2)*($I$2:$I$8>I2))-1)),"")

    This is an array formula, confirmed with Ctrl+Shift+Enter

    Then copy down
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    10-03-2014
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    15

    Re: Pull next date in unsorted data given criteria

    Thank you, but this doesn't appear to be working beyond the first 1 or 2 in my sheet. It simply keeps repeating the same date for the last date entry of the same column AD and J.

    Maybe I wasn't clear - I have thousands of unsorted items and I was hoping for an array formula that would take an entry and find the next date for each entry that had the identical column AD and column J. I can't thank you enough for your help.

+ 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. Pull Latest Date 2 Criteria Lookup
    By erock24 in forum Excel General
    Replies: 2
    Last Post: 08-25-2014, 09:47 PM
  2. [SOLVED] Pull a date when it meets criteria and it's text is bold
    By HCLax in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2014, 10:40 AM
  3. Replies: 6
    Last Post: 09-12-2011, 12:51 PM
  4. can't pull greatest date using criteria
    By wendyhatwork in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 10-22-2008, 03:47 PM
  5. Pull Data based on a date criteria
    By Nolesphan30 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2008, 12:22 AM

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