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

 

[awoohaw]

how do you determine which is the earliest date? The values in column F are not dates, they are text strings?
I know they are date ranges, but those do not sort easily as you have those date strings defined.

 

[awoohaw]

this is using a helper column for the first date:

Mr excel questions 35.xlsm
	A	B	C	D	E	F
1
2
3
4					Time 1	22/5/2023 - 4/6/2023
5					Time 2	8/5/2023 - 21/5/2023
6					Time 3
7					Time 4
8					Time 5
9
10					2023-05-22	22/5/2023 - 4/6/2023
11
12
13
14
15
16
17
18
19
20					2023-05-08	8/5/2023 - 21/5/2023
21
22
23
woah

Cell Formulas
Range	Formula
F4:F8	F4	=IFERROR(INDEX($F$10:$F$29,AGGREGATE(15,6,1/(1/((--(ISNUMBER($E$10:$E$29))*(ROW($E$10:$E$29)-9)))),ROWS($E$10:$E10))),"")
E10	E10	=DATE(2023,5,22)
E20	E20	=DATE(2023,5,8)

 

[DanteAmor]

It is a formula array. It's long but you get what you need.

Dante Amor
	A	B	C	D	E	F	G
1
2
3
4					Time 1	15/4/2023 - 30/4/2023
5					Time 2	1/5/2023 - 7/5/2023
6					Time 3	8/5/2023 - 21/5/2023
7					Time 4	22/5/2023 - 4/6/2023
8					Time 5	5/6/2023 - 10/6/2023
9
10
11						22/5/2023 - 4/6/2023
12
13
14						8/5/2023 - 21/5/2023
15
16
17						5/6/2023 - 10/6/2023
18
19
20						1/5/2023 - 7/5/2023
21
22
23						15/4/2023 - 30/4/2023
24
Hoja8 (2)

Cell Formulas
Range	Formula
F4:F8	F4	=INDEX($F$11:$F$100,MAX((SMALL(IF($F$11:$F$100<>"",DATEVALUE(LEFT($F$11:$F$100,FIND("-",$F$11:$F$100)-2))),ROWS(E$4:E4))=IF($F$11:$F$100<>"",DATEVALUE(LEFT($F$11:$F$100,FIND("-",$F$11:$F$100)-2))))*(ROW($F$11:$F$100)))-ROW($F$11)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

----- --
This is another shorter option, but the dates should be as in your example: "8/5/2023 - 21/5/2023", that is, without the leading zero: 05/08/2023 - 05/21/2023

Dante Amor
	A	B	C	D	E	F	G
1
2
3
4					Time 1	15/4/2023 - 30/4/2023
5					Time 2	1/5/2023 - 7/5/2023
6					Time 3	8/5/2023 - 21/5/2023
7					Time 4	22/5/2023 - 4/6/2023
8					Time 5	5/6/2023 - 10/6/2023
9
10
11						22/5/2023 - 4/6/2023
12
13
14						8/5/2023 - 21/5/2023
15
16
17						5/6/2023 - 10/6/2023
18
19
20						1/5/2023 - 7/5/2023
21
22
23						15/4/2023 - 30/4/2023
24
Hoja8

Cell Formulas
Range	Formula
F4:F8	F4	=VLOOKUP(TEXT(SMALL(IF($F$11:$F$100<>"",DATEVALUE(LEFT($F$11:$F$100,FIND("-",$F$11:$F$100)-2))),ROWS(E$4:E4)),"d/m/aaaa")&"*",$F$11:$F$100,1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Peter_SSs]

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.

Is it possible with your data that 2 (or more) periods can start on the same date?

 

[woah]

Is it possible with your data that 2 (or more) periods can start on the same date?

Unfortunately, they sometimes start with the different date

 

[Peter_SSs]

Unfortunately, they sometimes start with the different date

That isn't entirely clear to me. My question was

Is it possible with your data that 2 (or more) periods can start on the same date?

That is could there be data like this where both of the yellow periods start on 15 April 2023?

23 05 11.xlsm
	F
11	22/5/2023 - 4/6/2023
12
13
14	15/4/2023 - 21/5/2023
15
16
17	5/6/2023 - 10/6/2023
18
19
20
21
22
23	15/4/2023 - 30/4/2023
Extract List

 

[woah]

That isn't entirely clear to me. My question was

That is could there be data like this where both of the yellow periods start on 15 April 2023?

23 05 11.xlsm
	F
11	22/5/2023 - 4/6/2023
12
13
14	15/4/2023 - 21/5/2023
15
16
17	5/6/2023 - 10/6/2023
18
19
20
21
22
23	15/4/2023 - 30/4/2023
Extract List

Yes, it could be. Your photo is one of the examples that I will come across with. Thanks.

 

[woah]

It is a formula array. It's long but you get what you need.

Dante Amor
	A	B	C	D	E	F	G
1
2
3
4					Time 1	15/4/2023 - 30/4/2023
5					Time 2	1/5/2023 - 7/5/2023
6					Time 3	8/5/2023 - 21/5/2023
7					Time 4	22/5/2023 - 4/6/2023
8					Time 5	5/6/2023 - 10/6/2023
9
10
11						22/5/2023 - 4/6/2023
12
13
14						8/5/2023 - 21/5/2023
15
16
17						5/6/2023 - 10/6/2023
18
19
20						1/5/2023 - 7/5/2023
21
22
23						15/4/2023 - 30/4/2023
24
Hoja8 (2)

Cell Formulas
Range	Formula
F4:F8	F4	=INDEX($F$11:$F$100,MAX((SMALL(IF($F$11:$F$100<>"",DATEVALUE(LEFT($F$11:$F$100,FIND("-",$F$11:$F$100)-2))),ROWS(E$4:E4))=IF($F$11:$F$100<>"",DATEVALUE(LEFT($F$11:$F$100,FIND("-",$F$11:$F$100)-2))))*(ROW($F$11:$F$100)))-ROW($F$11)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

----- --
This is another shorter option, but the dates should be as in your example: "8/5/2023 - 21/5/2023", that is, without the leading zero: 05/08/2023 - 05/21/2023

Dante Amor
	A	B	C	D	E	F	G
1
2
3
4					Time 1	15/4/2023 - 30/4/2023
5					Time 2	1/5/2023 - 7/5/2023
6					Time 3	8/5/2023 - 21/5/2023
7					Time 4	22/5/2023 - 4/6/2023
8					Time 5	5/6/2023 - 10/6/2023
9
10
11						22/5/2023 - 4/6/2023
12
13
14						8/5/2023 - 21/5/2023
15
16
17						5/6/2023 - 10/6/2023
18
19
20						1/5/2023 - 7/5/2023
21
22
23						15/4/2023 - 30/4/2023
24
Hoja8

Cell Formulas
Range	Formula
F4:F8	F4	=VLOOKUP(TEXT(SMALL(IF($F$11:$F$100<>"",DATEVALUE(LEFT($F$11:$F$100,FIND("-",$F$11:$F$100)-2))),ROWS(E$4:E4)),"d/m/aaaa")&"*",$F$11:$F$100,1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

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.

 

[woah]

That isn't entirely clear to me. My question was

That is could there be data like this where both of the yellow periods start on 15 April 2023?

23 05 11.xlsm
	F
11	22/5/2023 - 4/6/2023
12
13
14	15/4/2023 - 21/5/2023
15
16
17	5/6/2023 - 10/6/2023
18
19
20
21
22
23	15/4/2023 - 30/4/2023
Extract List

Some of the data might the same start date but with different end date, or vice versa. Just like this.