Extracting the earliest and latest (Time)

[-emma-]

Hi all,

Some information is produced at work and I need to pull certain information from it - which I can do but it takes a long time. I am sure some smart person can work something for me to make it so much easier for me.
Below is what we get produced:

John	00:00	07:56
John	07:56	12:02
John	12:02	12:36
John	12:36	16:15
John	16:15	+00:00
Emma	00:00	09:00
Emma	09:00	17:00
Emma	17:00	+00:00

What I need pulling is below:

John	Earliest	07:56
John	Latest	16:15
Emma	Earliest	09:00
Emma	Latest	17:00

I would really appreciate someones help on this please - Thank you!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have Microsoft 365, you could see if this does what you want.

22 02 21.xlsm
	A	B	C
1
2	John	0:00	7:56
3	John	7:56	12:02
4	John	12:02	12:36
5	John	12:36	16:15
6	John	16:15	+00:00
7	Emma	0:00	9:00
8	Emma	9:00	17:00
9	Emma	17:00	+00:00
10
11
12	John	Earliest	7:56
13	John	Latest	16:15
14	Emma	Earliest	9:00
15	Emma	Latest	17:00
16
Earliest Latest

Cell Formulas
Range	Formula
A12:A15	A12	=LET(u,UNIQUE(A2:A9),INDEX(u,SEQUENCE(2*ROWS(u),,,0.5)))
B12:B15	B12	=INDEX({"Earliest","Latest"},MOD(ROW(A12#)-ROW(B$12),2)+1)
C12:C15	C12	=IF(B12#="Earliest",MINIFS(C$2:C$9,A$2:A$9,A12#),MAXIFS(B$2:B$9,A$2:A$9,A12#))
Dynamic array formulas.

 

[-emma-]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have Microsoft 365, you could see if this does what you want.

22 02 21.xlsm
	A	B	C
1
2	John	0:00	7:56
3	John	7:56	12:02
4	John	12:02	12:36
5	John	12:36	16:15
6	John	16:15	+00:00
7	Emma	0:00	9:00
8	Emma	9:00	17:00
9	Emma	17:00	+00:00
10
11
12	John	Earliest	7:56
13	John	Latest	16:15
14	Emma	Earliest	9:00
15	Emma	Latest	17:00
16
Earliest Latest

Cell Formulas
Range	Formula
A12:A15	A12	=LET(u,UNIQUE(A2:A9),INDEX(u,SEQUENCE(2*ROWS(u),,,0.5)))
B12:B15	B12	=INDEX({"Earliest","Latest"},MOD(ROW(A12#)-ROW(B$12),2)+1)
C12:C15	C12	=IF(B12#="Earliest",MINIFS(C$2:C$9,A$2:A$9,A12#),MAXIFS(B$2:B$9,A$2:A$9,A12#))
Dynamic array formulas.

Hi,

Thank you for the advice on updating my account details - I have done that now - and I am using Microsoft 365.


Also, thank you for your help with the solution - however the column A doesn't seem to want to work for me. I have attached a screen show below. Do you think it is something I am doing?

Thanks again!

Emma

 

[Peter_SSs]

I have done that now - and I am using Microsoft 365.

Great. Thanks.

however the column A doesn't seem to want to work for me.

Looks like you do not have the LET function (perhaps you have some updates to do?) so you should be able to use this instead.

22 02 21.xlsm
	A	B	C
1
2	John	0:00	7:56
3	John	7:56	12:02
4	John	12:02	12:36
5	John	12:36	16:15
6	John	16:15	+00:00
7	Emma	0:00	9:00
8	Emma	9:00	17:00
9	Emma	17:00	+00:00
10
11
12	John	Earliest	7:56
13	John	Latest	16:15
14	Emma	Earliest	9:00
15	Emma	Latest	17:00
16
Earliest Latest (2)

Cell Formulas
Range	Formula
A12:A15	A12	=INDEX(UNIQUE(A2:A9),SEQUENCE(2*ROWS(UNIQUE(A2:A9)),,,0.5))
B12:B15	B12	=INDEX({"Earliest","Latest"},MOD(ROW(A12#)-ROW(B$12),2)+1)
C12:C15	C12	=IF(B12#="Earliest",MINIFS(C$2:C$9,A$2:A$9,A12#),MAXIFS(B$2:B$9,A$2:A$9,A12#))
Dynamic array formulas.

 

[-emma-]

Great. Thanks.


Looks like you do not have the LET function (perhaps you have some updates to do?) so you should be able to use this instead.

22 02 21.xlsm
	A	B	C
1
2	John	0:00	7:56
3	John	7:56	12:02
4	John	12:02	12:36
5	John	12:36	16:15
6	John	16:15	+00:00
7	Emma	0:00	9:00
8	Emma	9:00	17:00
9	Emma	17:00	+00:00
10
11
12	John	Earliest	7:56
13	John	Latest	16:15
14	Emma	Earliest	9:00
15	Emma	Latest	17:00
16
Earliest Latest (2)

Cell Formulas
Range	Formula
A12:A15	A12	=INDEX(UNIQUE(A2:A9),SEQUENCE(2*ROWS(UNIQUE(A2:A9)),,,0.5))
B12:B15	B12	=INDEX({"Earliest","Latest"},MOD(ROW(A12#)-ROW(B$12),2)+1)
C12:C15	C12	=IF(B12#="Earliest",MINIFS(C$2:C$9,A$2:A$9,A12#),MAXIFS(B$2:B$9,A$2:A$9,A12#))
Dynamic array formulas.

Peter - this worked great for me! Thank you for your time and effort on this.

Regarding the update - As its a work PC and I am not the admin, these things are beyond my control. I have however escalated it to our IT department as I also found some functions with PowerPoint disabled (for no reason) for users.

Thanks again for your help on this

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.