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!