I have this list of events (A) that has a start (B) and end date (C). Now what I try to achieve is to create a new list of days (E), like a calendar, that collects all events taking place that day (F).
Meaning if Event A is on Jan 1 - 3. And Event B is on Jan 2 - 4. It should look like this:
- jan: Event A
- jan: Event A + Event B
- jan: Event A + Event B
- jan: Event B
I have tried FILTER, VLOOKUP, IF and IFERROR without any luck simply due to the fact that I stuggle with the formula embracing the "in between dates" aspect.
I have succeeded using both filters and Vlookup to sort out everything happening based on one of the values B or C, but not the >=B AND <=C combined.
For that, what does work is simply saying =IF(AND(E3>=B3,E3<=C3),A3,"") It would successfully add the first event. And I could copy this code onto G,H,I.. horizontally to check for A4,A5,A6,A7 etc. The problem is, when that list of events is 500 rows long, it just makes no sense to set up those 500 cells of code individually - when there should be an easier way to have this automated as an array?
Clearly I must be missing just that little extra code to have this work. Hoping anyone in here are able to see the details in pulling this off?
For the record, I am prepared to get to the final result in at least two steps. First pull each event into individual cells, and then for a second table concatenate that info into the final one cell with TEXTJOIN. (=TEXTJOIN(" + ",TRUE,F3:Z3,))