Index of terms based on overlapping dates

[elbickel]

Hi everyone,
I need help with a solution for how to determine and output the order and combinations of variables occurring over time for different people.

Order of variables sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
2	name	variable 1	start date 1	end date 1	variable 2	start date 2	end date 2	variable 3	start date 3	end date 3	variable 4	start date 4	end date 4	variable 5	start date 5	end date 5	variable 6	start date 6	end date 6	variable 7	start date 7	end date 7	variable 8	start date 8	end date 8	variable 9	start date 9	end date 9	variable 10	start date 10	end date 10	1st variable combination	2nd variable combination	3rd variable combination	4th variable combination	5th variable combination	6th variable combination	7th variable combination	8th variable combination	9th variable combination	10th variable combination
3	name1	A	01/01/2010	01/01/2015	B	01/01/2014		C	01/01/2015		A	01/01/2018		E	01/01/2020																	A	A + B	B + C	A + B + C	A + B + C + E
4	name2	G	01/01/2015	01/01/2018	B	01/01/2015	01/01/2016	E	01/01/2019		A	01/01/2019																				B + G	G	A + E
5	name3
6	name4
7	name5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B3:B7	List	=$A$10:$A$16
E3:E7	List	=$A$10:$A$16
H3:H7	List	=$A$10:$A$16
K3:K7	List	=$A$10:$A$16
N3:N7	List	=$A$10:$A$16
Q3:Q7	List	=$A$10:$A$16
T3:T7	List	=$A$10:$A$16
W3:W7	List	=$A$10:$A$16
Z3:Z7	List	=$A$10:$A$16
AC3:AC7	List	=$A$10:$A$16

This XL2BB shows 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. 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 the order of each combination of variables based on the recorded dates of each variable and whether they overlap (hopefully demonstrated by the 2 diagrams below).

Order of variables sheet.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P
8
9	Name 1	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
10	A	01/01/2010					01/01/2015			01/01/2018
11	B					01/01/2014
12	C						01/01/2015
13	E											01/01/2020
14
15
16
17
18
19
20
21
22	Name 2	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
23	A									01/01/2018
24	B						01/01/2015	01/01/2016
25	E									01/01/2018
26	G						01/01/2015			01/01/2018
27
28
29
30
31
32
33
Sheet1

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 would really appreciate any help or ideas with how to do this!

 

[elbickel]

Here is a screenshot of the diagrams: