Hi, i have a complex sorting problem. To see clearly my example below, please copy and paste it into a excel spreadsheet.
This table provided is a example and i have hundreds of lines of such data. Please assume the "case id" is a unique id of a person. the "session id" is a unique id for the session. there can be any number of "case id's" associated with a session. the last column is "list order". this is the order in which each "case id" should be carried out. however, as shown in the example, the times will indicate that iften the list order is not adhered to and often random ordering is apparent.
It is easy to assume that simply sorting by "actual start" should put then in order of when they were actually undertaken. however, if the "actual start" field was not entered or missing, then the null value is shifted to the top!

can any1 help?


problem data:

case id session id date timetabled session start preperation start a b c d actual start actual finish g h i j List_order
23933 7644 02-Jun-07 08:30 16:10 16:15 16:20 16:52 17:10 17:12 1
23921 7644 02-Jun-07 08:30 09:26 10:15 10:17 13:05 13:25 13:30 14:25 14:45 2
23922 7644 02-Jun-07 08:30 14:30 14:35 14:55 15:36 15:41 15:43 15:50 16:00 3
23923 7644 02-Jun-07 08:30 18:00 18:17 18:25 18:46 18:47 18:49 4
23934 7644 02-Jun-07 08:30 19:41 20:01 20:07 20:24 20:30 20:32 5
23936 7645 03-Jun-07 08:30 11:36 12:04 12:10 13:05 1
23937 7645 03-Jun-07 08:30 14:15 14:36 14:37 15:10 15:13 16:10 16:20 2
23938 7645 03-Jun-07 08:30 17:40 17:59 17:59 19:30 19:35 3
23941 7645 03-Jun-07 08:30 20:20 20:25 20:25 22:10 22:15 4
23797 7912 01-Jun-07 08:30 08:00 08:00 09:00 09:05 09:20 09:32 10:36 11:37 11:45 1
23798 7912 01-Jun-07 08:30 15:31 15:41 16:03 17:20 17:38 17:50 2
23799 7912 01-Jun-07 08:30 10:14 10:14 10:27 10:45 10:48 11:37 11:50 12:00 12:00 13:37 14:00 3
23800 7912 01-Jun-07 08:30 4
23802 7912 01-Jun-07 08:30 11:45 11:47 12:08 12:19 12:31 14:10 14:20 14:20 15:30 15:59 5
23825 7914 01-Jun-07 08:30 09:15 09:15 09:29 09:43 10:00 10:05 11:33 11:40 11:40 12:55 13:00 1
23823 7914 01-Jun-07 08:30 2
23824 7914 01-Jun-07 08:30 3
this is how the above data should look:
case id session id date timetabled session start preperation start a b c d actual start actual finish g h i j List_order
23921 7644 02-Jun-07 08:30 09:26 10:15 10:17 13:05 13:25 13:30 14:25 14:45 2
23922 7644 02-Jun-07 08:30 14:30 14:35 14:55 15:36 15:41 15:43 15:50 16:00 3
23933 7644 02-Jun-07 08:30 16:10 16:15 16:20 16:52 17:10 17:12 1
23923 7644 02-Jun-07 08:30 18:00 18:17 18:25 18:46 18:47 18:49 4
23934 7644 02-Jun-07 08:30 19:41 20:01 20:07 20:24 20:30 20:32 5
23936 7645 03-Jun-07 08:30 11:36 12:04 12:10 13:05 1
23937 7645 03-Jun-07 08:30 14:15 14:36 14:37 15:10 15:13 16:10 16:20 2
23938 7645 03-Jun-07 08:30 17:40 17:59 17:59 19:30 19:35 3
23941 7645 03-Jun-07 08:30 20:20 20:25 20:25 22:10 22:15 4
23797 7912 01-Jun-07 08:30 08:00 08:00 09:00 09:05 09:20 09:32 10:36 11:37 11:45 1
23799 7912 01-Jun-07 08:30 10:14 10:14 10:27 10:45 10:48 11:37 11:50 12:00 12:00 13:37 14:00 3
23800 7912 01-Jun-07 08:30 4
23802 7912 01-Jun-07 08:30 11:45 11:47 12:08 12:19 12:31 14:10 14:20 14:20 15:30 15:59 5
23798 7912 01-Jun-07 08:30 15:31 15:41 16:03 17:20 17:38 17:50 2
23825 7914 01-Jun-07 08:30 09:15 09:15 09:29 09:43 10:00 10:05 11:33 11:40 11:40 12:55 13:00 1
23823 7914 01-Jun-07 08:30 2
23824 7914 01-Jun-07 08:30 3