Sort

[Unknown Soldier]

I have two worksheets, one called schedule, one called sort. I post this
question earlier and some folks are coming up with good help, but the help
stopped short.

The schedule worksheet look like this.

name start end
a 9:00 AM 5:00 PM
b
c 6:00 AM 12:00 PM
d 3:00 PM 9:00 PM
e 3:00 PM 8:00 PM


And the sort worksheet should look like this with formulas drag down:

Name Start End
c 6:00 AM 12:00 PM
a 9:00 AM 5:00 PM
d 3:00 PM 9:00 PM
e 3:00 PM 8:00 PM

Notice the sort worksheet are sort by time start and leaves out name without
a time start or end.

I got two folks who came up with two solutions.

One is sorting like I want to with time start, but the formulas won't take
duplicate, and the set back it that the worksheet is not taking duplicate
time start. Here they are.
Put this formula in B2 of Sheet2:

=IF(ISERROR(SMALL(Sheet1!B$2:B$6,ROW(A1))),"",SMALL(Sheet1!B$2:B
$6,ROW(A1)))

This will list your start times in sequence. Then put this in A2:

=IF(B2="","",INDEX(Sheet1!A$2:A$6,MATCH(B2,Sheet1!B$2:B$6,0)))

and this one in C2:

=IF(B2="","",INDEX(Sheet1!C$2:C$6,MATCH(B2,Sheet1!B$2:B$6,0)))

Copy the formulae down into rows 3:5.
Another folk came up with these formulas that take duplicate, but it does
not sort right, in the right order of starting time. Here they are:

A1: =IF(NOT(ISERROR(MATCH(Sheet1!$B1,Sheet1!$B$1:$B$200))),ROW(),"")
B1:
=IF(ROW()>COUNT($A$1:$A$200),"",INDEX(Sheet1!$A$1:$A$200,SMALL($A$1:$A$200,ROW())))
C1:
=IF(ROW()>COUNT($A$1:$A$200),"",INDEX(Sheet1!$B$1:$B$200,SMALL($A$1:$A$200,ROW())))
D1:
=IF(ROW()>COUNT($A$1:$A$200),"",INDEX(Sheet1!$C$1:$C$200,SMALL($A$1:$A$200,ROW())))


Is there a way or a formulas where I can have the best of both world?
Thanks

 

[Max]

Is there a way or a formulas where I can have the best of both world?
Here's one non-array formulas way to get there ..
Illustrated in this sample:
http://www.freefilehosting.net/download/3e7mb
AutoSort table by start times in another sht.xls

Source data as posted in "schedule" cols A to C, data from row2 down

In "sort",
In A2:
=IF(COUNT(schedule!B2:C2)<2,"",schedule!B2+ROW()/10^10)
Leave A1 blank

In B2:
=IF(ISERROR(SMALL($A:$A,ROWS($1:1))),"",INDEX(schedule!A:A,MATCH(SMALL($A:$A,ROWS($1:1)),$A:$A,0)))
Copy B2 to D2. Select A22, copy down to cover the max expected extent of
data in "schedule". Minimize/hide away col A. Cols B to D will return the
required results, all neatly bunched at the top. Lines with same start
times, if any, will appear in the same relative order that these are within
the source data.