Results 1 to 7 of 7

Thread: How can I combine a series of dates? VLOOKUP can only work with one unique identity.

  1. #1

    How can I combine a series of dates? VLOOKUP can only work with one unique identity.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have 2 worksheets I am working from.

    The first contains a list of date ranges. See the table below for examples, the data could be a single date range (which would be easy in the end), or consecutive dates ranges in the example of identifier 222222, or non consecutive date ranges as in the example of identifier 333333.
    Identifier Start Date End Date
    111111 01-Jan-2014 17-Mar-2014
    222222 12-Jan-2014 14-Feb-2014
    222222 15-Feb-2014 01-Apr-2014
    333333 16-Jan-2014 15-March-2014
    333333 01-Apr-2014 13-May-2014
    333333 27-May-2014 28-Jun-2014


    The second worksheets contains a single date with corresponding identifiers
    Identifier Date Include?
    111111 14-Feb-2014 No
    111111 01-Apr-2014 Yes
    222222 01-Apr-2014 No
    222222 15-Jun-2014 Yes
    333333 01-Apr-2014 No
    333333 15-May-2014 Yes

    What I would like to do is check the date by the first worksheet and if it is within the date range specified I want to have another column(In Red) that will tell me if it is on the list or not.

    This would be easy if there was only 1 date range; I could use VLOOKUP and an IF(AND()) equation to work it out
    This would still be within my limits if all the dates were consecutive; that way I could do a pivotable and take the minimum of the start dates and max of the end dates to create one date range.
    But with non-consecutive date ranges I am stumped at where to go to next.

    HELP...

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    To get the results you show, try:

    =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&B2,Sheet2!C:C,">="&B2)=0,"Yes","No")

    copied down

    where Sheet2 is the database,


  3. #3
    Thanks NBVC for the advice and the introduction to a newequation.
    I simplified my worksheets for the question. My start dates are in column E so I changedyour equation from B:B to E:E and the end date is in column G so I also changed C:C to G:G to no avail. All 44K lines of my formula come out as Yes.
    What does the &B2 do? I tried playing with that as well to A2, and then E2 and G2 respectively but that doesn’t make any “No” as well.

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    the &B2 is your criteria to search against. It refers to 14-Feb-2014 in the following table. where B1 is "Date"
    Identifier Date Include?
    111111 14-Feb-2014 No
    111111 01-Apr-2014 Yes
    222222 01-Apr-2014 No
    222222 15-Jun-2014 Yes
    333333 01-Apr-2014 No
    333333 15-May-2014 Yes


    so your formula using the above table, cell c2 should contain, =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!E:E,"<="&B2,Sheet2!G:G,">="&B2)=0,"Yes","No")

  5. #5
    Thanks Simi and NBVC,

    It all worked out in the end. I had one set of unique identifiers with leading zeroes which was fine when I conevrted them all over to numbers.

    Works like a gem!
    Last edited by alderman; 2014-01-23 at 11:42 PM. Reason: typos

  6. #6
    Let's push this a step further.

    Is there a way to retrieve a fourth column of data from the first worksheet of "identifier", "startDate", "end date", and for argument sake lets say "colour" and get the equation to return the colour instead of a "yes"?
    Last edited by alderman; 2014-01-27 at 08:13 PM. Reason: typos

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assumint same structure, with the addition of column D in Sheet2 that contain color name strings... Try something like:

    =IFERROR(INDEX(Sheet2!$D$2:$D$100,MATCH(1,INDEX((Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100<=B2)*(Sheet2!$C$2:$C$100>=B2),0),0)),"")

    Notice: This is an array formula and is less efficient than COUNTIFS, so it is recommended to use defined ranges that don't exceed maximum rows required.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •