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.
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).
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!
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!
