Way to sort Unique Values by time

[RedOctoberKnight]

I'm using an array formula to find unique values in a list of shift times. My question is if there is a way to have that list sorted by time starting with the earliest?

{=IFERROR(INDEX($Z$4:$Z$35, MATCH(0, COUNTIF($AN$3:$AN4, $Z$4:$Z$35), 0)),"")}

Shift times are in the following format:

5:30-14:00
6:30-15:00
13:00-21:30
OFF

There will be more times than above. Was wondering if its able to sort them by the earliest start time.

 

[Eric W]

I spent too long on this! but it was interesting. Try:

Book4
	Z														AN
1
2
3
4	5:30-14:00														Shifts
5	6:30-15:00														2:00-11:30
6	13:00-21:30														5:30-14:00
7	OFF														6:30-15:00
8	6:30-15:00														8:00-9:00
9	13:00-21:30														8:00-9:45
10	8:00-9:00														13:00-21:30
11	21:00-23:45														21:00-23:45
12	2:00-11:30														23:00-7:30
13	6:30-15:00
14	23:00-7:30
15	8:00-9:45
16
Sheet1

Cell Formulas
Range	Formula
AN5:AN15	AN5	=IFERROR(TEXT(AGGREGATE(15,6,(INT(LEFT(SUBSTITUTE($Z$4:$Z$35,"-"," "),5)*1440)+RIGHT(SUBSTITUTE($Z$4:$Z$35,"-","0"),5))/(COUNTIF($AN$4:$AN4,$Z$4:$Z$35)=0),1)/1440,"h:mm")&"-"&TEXT(MOD(AGGREGATE(15,6,(INT(LEFT(SUBSTITUTE($Z$4:$Z$35,"-"," "),5)*1440)+RIGHT(SUBSTITUTE($Z$4:$Z$35,"-","0"),5))/(COUNTIF($AN$4:$AN4,$Z$4:$Z$35)=0),1),1),"h:mm"),"")

You'll see that it ignores the OFF value.

 

[RedOctoberKnight]

wow, that worked perfectly! Thank you so much. I had no idea it would be that complicated. Thank you for taking the time to figure it out.

 

[Eric W]

Glad it works for you! Thanks for the feedback.