Formula to extract data from a list without blank space and sort it with the earliest date.

[woah]

Hi there,

I would like to extract the data from the list of period ("Column F") without the blank space, and sort it with the earliest date. Here is the example. I want it to come out with the result like cell F4 and F5.

What formula can I use for this case? Thank you very much

 

[Peter_SSs]

Yes, it could be.

Thanks.
I have also used a helper column (H) which could be hidden. See if this is headed in the right direction.

23 05 11.xlsm
	E	F	G	H
4	Time 1	15/4/2023 - 30/4/2023
5	Time 2	15/4/2023 - 21/5/2023
6	Time 3	22/5/2023 - 4/6/2023
7	Time 4	5/6/2023 - 10/6/2023
8	Time 5
9
10
11		22/5/2023 - 4/6/2023		22/05/2023
12
13
14		15/4/2023 - 30/4/2023		15/04/2023
15
16
17		5/6/2023 - 10/6/2023		5/06/2023
18
19
20
21
22
23		15/4/2023 - 21/5/2023		15/04/2023
24
25
Extract List

Cell Formulas
Range	Formula
F4:F8	F4	=IFERROR(INDEX(F$11:F$100,AGGREGATE(15,6,(ROW(F$11:F$100)-ROW(F$11)+1)/(H$11:H$100=SMALL(H$11:H$100,ROWS(F$4:F4))),COUNTIF(F$3:F3,TEXT(SMALL(H$11:H$100,ROWS(F$4:F4)),"d/m/yyyy")&"*")+1)),"")
H11:H25	H11	=IF(F11="","",LEFT(F11,FIND(" ",F11))+0)

 

[Peter_SSs]

If the above result is satisfactory for you then it probably makes more sense to use two helper columns and keep the formulas a bit simpler.

23 05 11.xlsm
	E	F	G	H	I
4	Time 1	15/4/2023 - 30/4/2023
5	Time 2	15/4/2023 - 21/5/2023
6	Time 3	22/5/2023 - 4/6/2023
7	Time 4	5/6/2023 - 10/6/2023
8	Time 5
9
10
11		22/5/2023 - 4/6/2023		22/05/2023	3
12
13
14		15/4/2023 - 30/4/2023		15/04/2023	1
15
16
17		5/6/2023 - 10/6/2023		5/06/2023	4
18
19
20
21
22
23		15/4/2023 - 21/5/2023		15/04/2023	2
24
25
Extract List (2)

Cell Formulas
Range	Formula
F4:F8	F4	=IFNA(INDEX(F$11:F$100,MATCH(ROWS(F$4:F4),I$11:I$100,0)),"")
H11:H25	H11	=IF(F11="","",LEFT(F11,FIND(" ",F11))+0)
I11:I25	I11	=IF(H11="","",RANK(H11,H$11:H$100,1)+COUNTIF(H$10:H10,H11))

 

[DanteAmor]

Thank you. However, if some of the data might the same start date but with different end date, or vice versa. Your formula won't work.

Just like this list.

View attachment 91417

OK. My formula doesn't work with duplicate data, because you hadn't mentioned it.

I give you another option, this option uses 3 helper columns and also checks if you have the same duplicate date range, you didn't mention it, but now I consider it too.

Dante Amor
	E	F	G	H	I
4	Time 1	15/4/2023 - 30/4/2023
5	Time 2	15/4/2023 - 21/5/2023
6	Time 3	22/5/2023 - 31/5/2023
7	Time 4	22/5/2023 - 31/5/2023
8	Time 5	22/5/2023 - 4/6/2023
9
10
11		22/5/2023 - 4/6/2023	22/05/2023	04/06/2023	5.011
12
13		15/4/2023 - 21/5/2023	15/04/2023	21/05/2023	2.013
14
15		5/6/2023 - 10/6/2023	05/06/2023	10/06/2023	6.015
16
17		15/4/2023 - 30/4/2023	15/04/2023	30/04/2023	1.017
18
19		22/5/2023 - 31/5/2023	22/05/2023	31/05/2023	3.019
20
21		22/5/2023 - 31/5/2023	22/05/2023	31/05/2023	3.021
22
Hoja8 (3)

Cell Formulas
Range	Formula
F4:F8	F4	=INDEX($F$11:$F$100,SUMPRODUCT(($I$11:$I$100=SMALL($I$11:$I$100,ROWS($E$4:E4)))*ROW($I$11:$I$100))-ROW($F$11)+1)
G21,G11:G19	G11	=IF(F11="","",LEFT(F11,FIND("-",F11)-2)+0)
H21,H11:H19	H11	=IF(F11="","",MID(F11,FIND("-",F11)+2,10)+0)
I11:I21	I11	=IF(G11="","",COUNTIF($G$11:$G$100,"<"&G11)+COUNTIFS($G$11:$G$100,G11,$H$11:$H$100,"<"&H11)+1+ROW()/1000)

I hope it helps you. Have a great day.