+ Reply to Thread
Results 1 to 14 of 14

identify distinct date ranges and sum associated product based on unique identifier

  1. #1
    Registered User
    Join Date
    12-18-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    6

    Post identify distinct date ranges and sum associated product based on unique identifier

    Hi,

    I am trying to identify unique patterns to ultimately count how many distinct absences a person has had, the start and end dates of those absences and the number of hours associated with each distinct absence. I am open to helper columns or vba - although I'm just learning vba myself. A sample data file is attached.

    Problem:
    I have a database of multiple absences sorted by individual along with the # hours for each absence and the associated start and end dates of the absence. The solution I am trying to achieve is to identify unique instances of absence ( which may be made up of multiple dates) and the total number of hours for each of these absences.



    Some contraints I am working with:
    - if the start date of one leave is within <=15 days of the end of the prior leave, the absence is considered continuous. If the time between these leaves is >15 days this is considered a new absence period.
    - often, there can be two lines with same start and end date for that person as the hours were classified into different types. The types are too numerous and varied to use them as a reliable filter. Unfortunately, I can't just ignore the lines with duplicate dates entries as I need to add the hours from each entry into my total.

    End result:
    To reduce the database into a separate sheet containing only one line per absence with the start and end dates of that absence period and the total hours associated with that time period. I was planning on filtering the data once all the calculations were done but if vba can help me pull out those unique lines, all the better.



    What I've tried:
    - Getting there in steps using helper columns

    A column to determine the time duration between the end date of one line and the start date of the next based on the Identifier being equal.
    =IFERROR(IF(A3=A4,SUM(C3-B4),0),"end of record")
    this works well but then I haven't been able to find the right formula to create the rules around how to determine which lines of Hours to add together based on the result

    I have also been able to use an array to find the min and max dates by identifier and put them in seprate columns but this doesn't work when a unique identifier has two or three distinct absences (>15 days apart)
    {=MIN(IF(A:A=A3,B:B))} and {=MAX(IF(A:A=A3,C:C))}


    Thanks in advance for your help.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Hi audreyc. Welcome to the forum.

    Wow.

    My first question:
    - often, there can be two lines with same start and end date for that person as the hours were classified into different types. The types are too numerous and varied to use them as a reliable filter. Unfortunately, I can't just ignore the lines with duplicate dates entries as I need to add the hours from each entry into my total.
    Do these types need to be accounted for separately? So far this is what I have. It includes the hours of all types/duplicate date entries.

    Is this what you mean?


    F
    G
    H
    I
    1
    Identifier
    Start Date
    End Date
    Hours
    2
    1
    2/13/2016
    2/21/2016
    60
    3
    2
    9/8/2014
    5/15/2015
    728
    4
    3
    7/7/2014
    4/7/2015
    512
    5
    4
    2/26/2016
    3/15/2016
    56
    6
    5
    8/17/2015
    8/21/2015
    40
    7
    6
    5/23/2016
    5/23/2016
    12
    8
    7
    3/19/2015
    3/25/2015
    40
    9
    8
    6/23/2017
    6/29/2017
    40
    10
    9
    4/7/2015
    12/25/2017
    3600
    11
    10
    2/22/2016
    8/25/2017
    126
    12
    11
    6/29/2015
    7/20/2015
    75


    - if the start date of one leave is within <=15 days of the end of the prior leave, the absence is considered continuous. If the time between these leaves is >15 days this is considered a new absence period.
    How do you want to handle weekends and holidays?
    Last edited by FlameRetired; 12-19-2017 at 07:59 PM.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Withdrawn by FR.

  4. #4
    Registered User
    Join Date
    12-18-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Thanks FlamRetired,

    No the different leave types don't need to be accounted for separately.

    Your results look similar to what I received but if you look closely at Identifier 9, there are two distinct periods of leave (more than 15 days apart) that need to be accounted for separately. the first one ending on 5/04/2016 for a total of 2000 hours and a second period from 25/12/2016 to 25/12/2017 for 1600 hours. The same for Identifier 10 with two distinct timer periods that need to have separate total hours and start/end dates.

    Resolving this is my biggest challenge and question.

    I don't need to account for weekends/holidays - just 15 calendar days.


    Audrey
    Last edited by audreyc; 12-19-2017 at 08:09 PM.

  5. #5
    Registered User
    Join Date
    12-18-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: identify distinct date ranges and sum associated product based on unique identifier

    I don't need to account for weekend and holidays - just 15 calendar days.

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

    Re: identify distinct date ranges and sum associated product based on unique identifier

    I use 2 helper columns: E to group records and H to sum hours
    E3:
    =IF(A3<>A2,A3*10+1,IF(B3-C2>15,E2+1,E2))
    H3:
    =IF(E3=E2,"",SUMIF($E$3:$E$27,E3,$D$3:$D$27))
    Quang PT

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: identify distinct date ranges and sum associated product based on unique identifier

    maybe with PowerQuery (Get&Transform)

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: identify distinct date ranges and sum associated product based on unique identifier

    In the attached there are two helper columns. In E
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In F
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in H the Identifiers both single and repeating. This is array entered.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then this formula in column I and filled across J
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then 'Hours' in K
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-18-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Thanks Quang bebo021999. Your answer works perfectly!

    I had one more piece of the puzzle to do and have re-applied the array {=MAX(IF(E:E=E3,C:C))} to a new column and it gives me the end data for that continuous occurence. However, the MAX array slows my computer's processors right down. Do you have any other suggestions on the best way to determine capture the Start and End dates of the each absence occurence?

    Apologies from not replying directly to your post but that functionality doesn't seem to be working for me - spinning blue circle.

    Audrey

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

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Quote Originally Posted by audreyc View Post
    {=MAX(IF(E:E=E3,C:C))}
    Try to avoid MAX(IF...) because Excel have to calculate each row one by one
    Also try to refer to specific range, not whole column.
    I suggest:
    =MAX(($E$3:$E$27=E3)*$C$3:$C$27)
    Ctrl-shift-enter

  11. #11
    Registered User
    Join Date
    12-18-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Quote Originally Posted by bebo021999 View Post
    Try to avoid MAX(IF...) because Excel have to calculate each row one by one
    Also try to refer to specific range, not whole column.
    I suggest:
    =MAX(($E$3:$E$27=E3)*$C$3:$C$27)
    Ctrl-shift-enter
    Thanks again. I works for Max but will not work for Min. It returens 01/01/1900 when used as an equivalent MIN array
    =MIN(($E$3:$E$27=E3)*$B$3:$B$27) ctrl-shfit-enter or returns just the value for each line of B when not used as an array. while I can work with the non=array version, why and how would the formula be different for MIN?

  12. #12
    Registered User
    Join Date
    12-18-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Thanks for your innovative solutions FlameRetired. I received a more straightforward answer to the calculation side from another poster but I will defnitely incorporate your H-K columns in filtering the final dataset.

    You've been a great help.

    Audrey

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Audrey,

    Glad to help.

    For what it's worth: As an alternative to my previous array entered formula for 'Identifiers' try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

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

    Re: identify distinct date ranges and sum associated product based on unique identifier

    Quote Originally Posted by audreyc View Post
    ($E$3:$E$27=E3)*$B$3:$B$27
    Min value of this always equal 0. So, for MIN, try to use =MIN(IF(...

    =MIN(IF($E$3:$E$27=E3,$B$3:$B$27,""))

    Try no to to refer to whole column like E:E.

+ 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] Identify difference in value between two columns based on matching of unique identifier
    By rn_ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2017, 07:38 PM
  2. Replies: 0
    Last Post: 02-09-2017, 01:20 PM
  3. Replies: 0
    Last Post: 02-09-2017, 01:10 PM
  4. Replies: 0
    Last Post: 02-09-2017, 01:03 PM
  5. Identify the dates or date ranges applicable based on a criteria
    By tiggi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-28-2016, 05:45 AM
  6. [SOLVED] Sum selected values in matrix based on date range as well as unique identifier in row
    By smansyd in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-09-2014, 07:56 PM
  7. formula to identify week number based on date ranges and add values
    By Lmendez in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-18-2014, 12:55 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