# Index of terms based on overlapping dates

1. ## 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!  Register To Reply

2. ## 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.  Register To Reply

3. ## 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)?  Register To Reply

4. ## 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.  Register To Reply

5. ## 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.  Register To Reply

6. ## Re: Index of terms based on overlapping dates

Thank you so much JeteMc, this is very helpful   Register To Reply

7. ## Re: Index of terms based on overlapping dates

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.  Register To Reply

8. ## Re: Index of terms based on overlapping dates

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

9. ## 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.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 