Sequentialize data from two columns

[excelos]

Hello

I have the data in the format below:

ID	Start	End	Origination	Destination
1​	01/01/2000​	02/01/2000​	GB	US
1​	02/01/2000​	03/01/2000​	DE	FR
1​	03/01/2000​	04/01/2000​	DK	FI
2​	04/01/2000​	05/01/2000​	ES	RO
2​	05/01/2000​	06/01/2000​	SE	IT

For each ID, I need to put into an array all the origination and destination locations in time order, so that e.g.:
for ID=1, it will be {GB, US, DE, FR, DK, FI}
and
for ID=2 it will be {ES, RO, SE, IT}

Any neat way to do that without helper columns?
Thanks!

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F	G
1	ID	Start	End	Origination	Destination
2	1	2000-01-01	2000-01-02	GB	US
3	1	2000-01-02	2000-01-03	DE	FR
4	1	2000-01-03	2000-01-04	DK	FI
5	2	2000-01-04	2000-01-05	ES	RO
6	2	2000-01-05	2000-01-06	SE	IT
7
8
9	1	GB	US	DE	FR	DK	FI
10	2	ES	RO	SE	IT
Sheet1

Cell Formulas
Range	Formula
A9:A10	A9	=LET(ID,SORT(UNIQUE(Sheet1!$A$2:$A$6)),ID)
B9:G9,B10:E10	B9	=LET(List,Sheet1!$A$2:$E$6,ID,A9,Org,FILTER(CHOOSECOLS(List,2,4),ID=Sheet1!$A$2:$A$6),Dest,FILTER(CHOOSECOLS(List,2,5),ID=Sheet1!$A$2:$A$6),TRANSPOSE(TOCOL(CHOOSECOLS(SORT(HSTACK(Org,Dest),1,1),2,4),1,FALSE)))
Dynamic array formulas.