+ Reply to Thread
Results 1 to 12 of 12

Excluding overlapping dates from similar items on different rows of a table.

  1. #1
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Excluding overlapping dates from similar items on different rows of a table.

    Hello all,

    At this point I am just nitpicking at my chart, but I think my brain has had just about enough.

    What I am trying to do is automate the overlap column of this table. The table has multiple rows of the same Item Number, but different Lot numbers that will be consumed at different days, and different rates. For maximum efficiency there must be some overlap, and that overlap will in turn increase the duration of how long the item will last. Further, since the weekends are already excluded from the duration by way of the WORKDAY function, the overlap should not count weekends when calculating overlapping days.

    I began a formula (see W2) that I thought would do what I needed, but my brain has fizzled out on trying to reference the correct cells of the appropriate start and end dates (I'm not very good at index matching, regardless).

    Anyways, take a look, and if you think you see a solution, please let me know.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Finally do you want to eliminate the overlap duration of specific item Number?
    i.e : Test 1, for the second one, row 7, start date will be from last end date, row 6? Last end date is 1/25/18 so start date will be 1/26/18?
    Quang PT

  3. #3
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Hi bebo, thanks for asking for that clarification!

    So, the purpose is not to eliminate the overlap per se, but to increase the duration when an overlap happens. The start date of the second Test1 (from row 7) is user defined and would be as enter 1/22/18, and since the first Test1(row 6) doesn't end until 1/25/18, there are 3 days of overlap. The table is already designed to calculate duration so that if you just input a 3 in F7, it will increase the duration of that Item by 3 days.

    It gets a even trickier if the overlap occurs over the course of the weekend. Consider the following example: If I move Item1(Row 6) to start on 1/9/18, its end date will be on 1/30/18. If I also move Item1(Row 7) to start on 1/25/18, it still only has 3 days of overlap instead of 5, since the weekends are already calculated in the overall duration.

    One more requirement is that only the Items that start after one of the same type gets an overlap added, so Item1(Row 6) will not have an overlap unless another Item1 has a start date before 1/9/18 AND an end date after 1/9/18.

    Also to note, there is no overlap with Item2 or Item3 in this example, because currently they are unique on the table. However if is fair to assume that will not typically be the case, and each Item type will have to be evaluated against others of the same type. I have included a couple more examples in this spreadsheet, and as you can should be able to see, the values in the Overlap column are what is expected as the proper input.

    Thanks again for looking!
    Attached Files Attached Files
    Last edited by dacheeba; 01-08-2018 at 12:08 AM. Reason: Adding detail

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Excluding overlapping dates from similar items on different rows of a table.

    May be you must calculate number of working days between 1st End date (H6) and 2nd Start Date (D7), to be known as overlap:
    In F7
    =NETWORKDAYS(D7,H6,Holidays[Date])-1

    And, with condition for Item number and dates not overlap:

    F7 to become:
    Please Login or Register  to view this content.
    Copy to whole column F

  5. #5
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Thanks for that bebo!

    Unfortunately, that code creates a circular reference to the end date (Column H), although I don't know why, because the end date does not output to the F column.

    Also, I'm not sure the code would have worked as written since the table is intended to be sorted and filtered, so I think any solution will unfortunately have to include some index matching or vlookup matching to be rigorous enough to deal with those changes as they occur. I could be wrong though, I would be thrilled if I was just looking at this wrong, and there was a really easy solution!

    Thanks again for offering your assistance!

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Can you upload the real file that show the circular reference?

  7. #7
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Sure, here you go.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Bumping for no response.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Excluding overlapping dates from similar items on different rows of a table.

    Formula in F7 is correct:
    =IF(OR(A7<>A6,D7>H6),0,NETWORKDAYS(D7,H6,Holidays[Date])-1)

    But in F8 (2 rows different)
    =IF(OR(A9<>A8,D9>H8),0,NETWORKDAYS(D9,H8,Holidays[Date])-1)
    ???
    Now try to copy F7 to F6 and from F8 to F12 again and it should work.

  10. #10
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Re: Excluding overlapping dates from similar items on different rows of a table.

    I'm not sure I understand what you are trying to do bebo. If I copy F7 to F6, the test conditions will just evaluate the table headers, and if I copy F8 down to F12, the evaluation fall off of the bottom of the table. Further, to do so causes an Inconsistent Calculation Error on top of the circular reference. Also, if F7 is correct, then why is it outputting 0 overlap days, instead of 3?

    As I mentioned before, the data set is dynamic, so any solution will have to look at the entire Column A(Item Number) part of the table. Table may be sorted by lot number or start date, or maybe I would need to filter Items out, and if that occurs, the duplicate Items could separated by many rows, but would still have to reflect the overlap.

    For instance, this is the same table but sorted by lot number. How would the formula still work when the data became distributed in this manner?

    Thanks again for taking your time trying to solve this, I really do appreciate it.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    Re: Excluding overlapping dates from similar items on different rows of a table.

    So, in working to a solution for this, I have encountered a odd problem.

    If I use the following formula:

    Please Login or Register  to view this content.
    it returns false, even when I use the formula evaluator, it will reduce all the way down to IF(TRUE,1,0), but it then it returns the false value instead. I am using Ctrl-Shift-Enter to enter it in as an array.

    Any ideas why?
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excluding overlapping dates from similar items on different rows of a table.

    I believe that it is displaying to zero, not because formula evaluates to false, but because it creates a circular reference. The Overlap formula in column F references the End Date. The End Date formula references the Adj. Duration and the Adj. Duration references the Overlap.
    If you select cell F6 and use the 'Trace Dependents' feature three times you'll see that the first dependent is G6, the second dependent is H6 and the third dependents include F6:F12.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Help in combining rows where dates are consecutive or overlapping
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2017, 05:10 PM
  2. How to progressively count similar items in a table?
    By TroyMcClure in forum Excel General
    Replies: 1
    Last Post: 05-12-2016, 11:25 AM
  3. Do not group similar items together - PIVOT TABLE
    By sourabhg98 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-10-2016, 10:55 AM
  4. Replies: 5
    Last Post: 04-14-2015, 04:27 AM
  5. [SOLVED] Find sum of range across 2 columns excluding overlapping values and gaps.
    By gutterball in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-10-2014, 03:59 PM
  6. Grouping similar but not exact items in Pivot Table
    By RobLaw in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-19-2011, 05:20 PM
  7. Excluding items from a linked table?
    By tangcla in forum Access Tables & Databases
    Replies: 2
    Last Post: 05-14-2009, 05:57 PM

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