Event timeline with a condition

[virtuosok]

Hi,
I have the attached source file (it's an export from an external database, I'm getting a fresh version daily so there are always some fresh additions).

Blinded output 003.xlsx

drive.google.com

The focus is on column A (site number) and then columns N-S with various event types in the header, and their dates within, I highlighted in green for better visibility. What I need is a set of formulas to create some kind of a timeline for a selected site #, on a separate tab.
This is an example of what I'm trying to get at (dates are arbitrary):

Site #	screening	randomization	screening	screening failure	treatment discontinuation	screening failure
148​	23-Sep-2020​	1-Oct-2020​	15-Nov-2020​	1-Jan-2021​	16-Feb-2021​	12-Mar-2021​

The problem is that these events are somewhat erratic, there's no particular sequence - it can be couple of screenings in a row followed by a randomization, or screening and then screening failure... or almost any other combination... so I can't pre-program event headers in the timeline. For each individual site #, there will be a different # of events, and a different sequence too, so I need the timeline to refresh after I key in a new site #. What's worse, it may well happen that there are several events happening on the same day for a specific site, and I need to pull them all in the timeline with their own header. In case of multiple events on a single day, there's no particular preference which one should come up first in the timeline, as long as they are all there. Is it even possible?
While I mainly use Excel 2016, I'll happily accept 365 version of the formula if that's a game changer...

 

[virtuosok]

Hi Fluff,
I have no idea how you are doing that, but this works oh so well! Yes indeed I need the output from N:S, and I've tried the above in 365 version...
wow.
WOW.
Thanks!
One last question... is it possible to replace all instances of "IWRS003!" within the formula with relative references which point to, let's say, cell A3?
I.e. when I type IWRS003 in A3, the formula scours IWRS003 tab, but when I type IWRS002 it scours IWRS002 tab.

 

[Fluff]

You can use

=LET(Fltr,FILTER(INDIRECT("'"&A3&"'!N1:S253"),(INDIRECT("'"&A3&"'!A1:A253")=A2)+(ROW(INDIRECT("'"&A3&"'!N1:S253"))=1)),Rws,ROWS(Fltr)-1,Qty,SEQUENCE(,Rws*COLUMNS(Fltr),0),Ary,INDEX(Fltr,MOD(Qty,Rws)+2,INT(Qty/Rws)+1),SORT(FILTER(CHOOSE({1;2},INDEX(Fltr,Qty*0+1,INT(Qty/Rws)+1),Ary+0),Ary<>"n/a"),2,,1))

although the formula will now be volatile.

 

[virtuosok]

Thanks Fluff! This works great!
As a last step, I have created a timeline to visualize the events on a chart. It works well for the sample site 165 (which has the highest number of events), but X axis gets wonky when I select any other site. I have tried to fix axis bounds manually, this doesn't help... wonder what I'm doing wrong.
The updated source document is now posted here:

enrollment workbook v.0.181 blinded.xlsx

drive.google.com

(tab "Test")

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks