+ Reply to Thread
Results 1 to 9 of 9

Index of terms based on overlapping dates

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    27

    Index of terms based on overlapping dates

    Hello everyone,
    I need help with a way to determine the order and combinations of variables occurring over time for different people.
    In my database each person has space for up to 10 recorded variables, which can occur in any order and for any duration on time. I am after a way to output, based on the recorded dates of each variable and whether they overlap, the order of each combination of variables (hopefully demonstrated by the 2 diagrams below the table on the example database I have included). The variables are recorded in chronological order based off their start date, with a 3 columns to enter the variable name, start date, and end date. Where there is a start date and no end date that means the variable is continuous to today, today is therefore assumed to be the end date. 2 variables may start on the same day and so these are just inputted next to each other.
    Ie. if a person has only 2 variables recorded and they have the same start date but the first variable (A) has an end date and the second variable (B) is continuous to today then the output I need is that the 1st variable combination = A+B and the second variable combination = B.
    I assume the INDEX function is the best to use for pulling the variables but I am not sure how to write a formula with it to get the outputs I need based off the overlapping date criteria.
    I have included an example database to show you the layout that I am working with. I have filled in example data for 'name1' and 'name2' in the blue section of the table and the output I am after in the orange section of the table.
    I would really appreciate any help or ideas with how to do this!
    Attached Files Attached Files

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

    Re: Index of terms based on overlapping dates

    Not exactly like the chart on Sheet1, however it may be something that you could work with.
    On Sheet2 the matrix type table (rows 1:3) is converted to a row-over-row table (A5:D14) and another column (E) is added to display end dates for variables that are ongoing.
    Column E is populated using: =IF([@[End Date]]<>"",[@[End Date]],TODAY())
    Cell H5 is a drop down linked to the names in column Z
    Cells H6:H14 are populated using an array entered formula: =IFERROR(INDEX(ProperTable[Variable], MATCH(0, IF(H$5=ProperTable[Name], COUNTIF(H$5:H5,ProperTable[Variable]), ""), 0)),"")
    Cells I5:U5 are populated using: =IF(YEAR(I5)< YEAR(TODAY()),DATE(SUM(YEAR(I5),1),1,1),"")
    Cells I6:U14 are populated using: =SUMPRODUCT((ProperTable[[Name]:[Name]]=$H$5)*(ProperTable[[Variable]:[Variable]]=$H6)*(ProperTable[[Start Date]:[Start Date]]<=I$5)*(ProperTable[[End Date 2]:[End Date 2]]>=I$5))
    Cells I6:U14 have the following custom formatting applied: 0;;;
    Cells I6:U14 have the following conditional formatting applied: Cell Value = 1 (green font and fill)
    Cells I15:U15 are populated using: =IF(I6=1,$H6,"")&IF(I7=1,"+"&$H7,"")&IF(I8=1,"+"&$H8,"")&IF(I9=1,"+"&$H9,"")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    02-01-2021
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    27

    Re: Index of terms based on overlapping dates

    Hi,
    I'm sorry this has taken me so long to reply to I have had a very busy couple of weeks... I have just worked my way through your solution and this is really cleverly done thank you!
    Would you say the only way to do this is by unpivoting the data into another table? My example table has obviously been simplified, and in reality we have data from over 1000 people that we need to assess the combinations and orders of the variables of so realistically I would have to have have the data unpivoting into a different sheet and then pull the variable combinations back to the table from there. Do you think this is the best way to do it?
    The output that I need is just the different variable combinations in chronological order auto-populating from the blue section into the orange section (AF:AO) of the table in my original post. I included the chart more to help you visualise what I was asking for... again for the ease of working with a big data set, is there a way to do the calculation so the output goes straight into columns AF:AO? This would take out the manual part of using the chart to fill in these columns.
    If this isn't possible then we may be able to work with the chart and use it as a calculator for manual input of the variable combinations so thank you it is very impressive

    P.S. if needed I can add "End date 2" columns to the source part of the data table (blue section)?

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

    Re: Index of terms based on overlapping dates

    I missed seeing the preferred output in the orange section the first time around, glad that you pointed it out in post #3.
    This is one possible way to get there, however there are some manual operations and there may be other/better methods using VBA of which I know too little to be of any help.
    In rows 1:3, columns A:P, on Sheet3 the table is in its original form except that =TODAY() is placed as a default for the End Date cells (once a date is placed in the cell it will overwrite the function).
    In rows 6:15 the data is put into an unpivoted state.
    In rows 18:27 the data is sorted by Name and then End Date (note that the rows highlighted in green have been switched). Also another column (E) is added to indicate where new rows will need to be manually inserted due to skips in the dates (Indicated by the word Insert highlighted yellow). The new column is populated using: =IF(AND(A19=A18,C19>SUM(D18,1)),"Insert","")
    In rows 30:40 new columns (E:K) representing the variables are added. The new columns are populated using: =IF($B31=E$30,E$30,IFERROR(IF(AND(AGGREGATE(14,6,$D30:$D$31/($A30:$A$31=$A31)/($B30:$B$31=E$30),1)>$C31,AGGREGATE(14,6,$D30:$D$31/($A30:$A$31=$A31)/($B30:$B$31=E$30),1)<=$D31),E$30,""),""))
    Column L displays the number of the variable combination using: =IF(A31<>A30,"Variable 1",IF(AND(A31=A30,C31=C30),N6,"Variable "&SUM(RIGHT(N6,1)+0,1)))
    Column M displays the combination using: =TRIM(E31&" "&F31&" "&G31&" "&H31&" "&I31&" "&J31&" "&K31)
    Back up in rows 1:3, columns R:V, the output is displayed using: =IFERROR(INDEX($M$31:$M$40,AGGREGATE(14,6,(ROW($M$31:$M$40)-ROW($M$30))/($A$31:$A$40=$A2)/($L$31:$L$40=R$1),1)),"")
    Let us know if you have any questions.

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

    Re: Index of terms based on overlapping dates

    Played around with this a bit more and have a Power Query (Get & Transform) advanced editor code to convert the pivoted data into an Excel table as modeled on Sheet 4:
    Please Login or Register  to view this content.
    The formulas that populate columns AC:AL and R:V are just modifications of those listed in post #4.
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    02-01-2021
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    27

    Re: Index of terms based on overlapping dates

    Thank you so much JeteMc, this is very helpful

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,413

    Re: Index of terms based on overlapping dates

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    02-01-2021
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    27

    Re: Index of terms based on overlapping dates

    Thank you I couldn't find how to do this, will do it now

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

    Re: Index of terms based on overlapping dates

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] difference between 2 dates in terms of minutes
    By welcome_to in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-18-2020, 06:26 AM
  2. Replies: 7
    Last Post: 06-30-2017, 12:20 AM
  3. Using index match to find terms on multiple tables?
    By tsiguy96 in forum Excel General
    Replies: 3
    Last Post: 08-12-2015, 05:25 AM
  4. [SOLVED] Overlapping Dates, Gaps in Dates, Double Counting
    By arunkushvaha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2013, 11:15 PM
  5. Overlapping Dates, Gaps in Dates, Double Counting
    By sglxl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2012, 02:46 AM
  6. [SOLVED] Difference between two dates in terms of years
    By Alexander_Golinsky in forum Excel General
    Replies: 7
    Last Post: 05-03-2012, 11:58 AM
  7. Calculating Dates in Terms of Months
    By Jessica in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-20-2005, 02:05 PM

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