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

 

[Crystalyzer]

Can you also post the raw data?

 

[virtuosok]

Hi Crystalyzer,
Sorry, is the link embedded in my original email not working? That's where the source file is posted on Google Drive.

 

[Crystalyzer]

ok got it

 

[Crystalyzer]

So per your example above for site 148 I see this data

Blinded output 003.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
60	148			United States	4000	n/a	Screen Failure	Inactive				Screen Failure	23 Sep 2020	23 Sep 2020	23 Sep 2020	n/a	n/a	n/a	n/a
61	148			United States	4034	n/a	Screen Failure	Inactive				Screen Failure	18 Dec 2020	18 Dec 2020	18 Dec 2020	n/a	n/a	n/a	n/a
62	148			United States	4086	n/a	Screen Failure	Inactive				Screen Failure	26 Feb 2021	26 Feb 2021	26 Feb 2021	n/a	n/a	n/a	n/a
63	148			United States	4184	n/a	Screen Failure	Inactive				Screen Failure	21 May 2021	21 May 2021	21 May 2021	n/a	n/a	n/a	n/a
64	148			United States	4226	n/a	Screen Failure	Inactive				Screen Failure	28 Jul 2021	24 Jun 2021	28 Jul 2021	n/a	n/a	n/a	n/a
IWRS003

What would be the expected output for site 148? Should we also be breaking on "Subject Number" to differentiate by subject?

 

[Toadstool]

Hi Virtuosok,

So you want to show in columns all entries for a specified Site?
This assumes the data is sorted in date sequence within site (which is was for the few I checked).

Your example doesn't match the entries for 148 (which only had 4 entries, all for "Screen Failure") so I'll use Site 165 which has 18 entries. Just copy the B1 and B2 formulae right as many columns as you'll ever need.

Virtuosok-Blinded output 003.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Site	Completion	Completion	Treatment Discontinuation	Visit 8 (Week 18)	Visit 7 (Week 14)	Visit 8 (Week 18)	Treatment Discontinuation	Visit 6 (Week 11)	Visit 7 (Week 14)	Screen Failure	Visit 6 (Week 11)	Visit 5 (Week 8)	Screen Failure	Visit 5 (Week 8)	Screen Failure	Screen Failure	Screen Failure	Screen Failure
2	165	06 May 2021	03 Jun 2021	26 Apr 2021	27 Jul 2021	09 Jul 2021	29 Jul 2021	08 Jun 2021	16 Jul 2021	29 Jul 2021	13 Apr 2021	21 Jul 2021	13 Jul 2021	13 Apr 2021	14 Jul 2021	20 Apr 2021	24 May 2021	08 Jun 2021	01 Jun 2021
Report

Cell Formulas
Range	Formula
B1:T1	B1	=IFERROR(INDEX(IWRS003!$L$2:$L$99999,AGGREGATE(15,6,ROW(IWRS003!$A$2:$A$99999)-ROW(IWRS003!$A$1)/(IWRS003!$A$2:$A$99999=$A$2),COLUMN()-COLUMN($A$1))),"")
B2:T2	B2	=IFERROR(INDEX(IWRS003!$M$2:$M$99999,AGGREGATE(15,6,ROW(IWRS003!$A$2:$A$99999)-ROW(IWRS003!$A$1)/(IWRS003!$A$2:$A$99999=$A$2),COLUMN()-COLUMN($A$1))),"")

Named Ranges
Name	Refers To	Cells
IWRS003!_FilterDatabase	=IWRS003!$A$1:$BE$253	B1:T2

 

[Fluff]

How about, for 365 only

Blinded output 003.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	148	Screening Date	Screen Failure Date	Screening Date	Screen Failure Date	Screening Date	Screen Failure Date	Screening Date	Screen Failure Date	Screening Date	Screen Failure Date
3		23/09/2020	23/09/2020	18/12/2020	18/12/2020	26/02/2021	26/02/2021	21/05/2021	21/05/2021	24/06/2021	28/07/2021
4
Sheet1

Cell Formulas
Range	Formula
B2:K3	B2	=LET(Fltr,FILTER(IWRS003!N1:S253,(IWRS003!A1:A253=A2)+(ROW(IWRS003!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,SEQUENCE(,Rws*COLUMNS(Fltr),1,0),INT(Qty/Rws)+1),Ary),Ary<>"n/a"),2,,1))
Dynamic array formulas.

But you will need to converts your "dates" in columns N:S into real dates rather than text.

 

[virtuosok]

Thank you all! Toadstool it looks like the proposed solution works! I will test it in the next few days and will revert if any questions.
Have a great weekend all!

 

[virtuosok]

Hi Fluff, the solution works as well, but converting the dates is not very practical in my particular situation. However this is a very unconventional approach and I am learning a LOT by just sifting through the proposed formula... greatly appreciated as I'm going through differences in Excel 365 vs 2016....

 

[Fluff]

I thought you were after the details in columns N:S, not cols L & M?

If you don't want to convert the dates you can use this instead

=LET(Fltr,FILTER(IWRS003!N1:S253,(IWRS003!A1:A253=A2)+(ROW(IWRS003!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,SEQUENCE(,Rws*COLUMNS(Fltr),1,0),INT(Qty/Rws)+1),Ary+0),Ary<>"n/a"),2,,1))