sorting question

[Brian]

in column A from 31 down to 51 military time will be entered along with a
corresponding note in column B also from 31 down to 51.

These times and notes are used to document occurences during a work shift.
and notes often entered out of order. I want to sort the times at the end of
a work shiftso that they will be placed in order along with notes to the
right.

PROBLEM: There must be two separate worksheets for two different shifts.
the first shift starts at 0340 and ends at 1540 no problem when sorting
these times from the beggining of shift to its end, HOWEVER, the second shift
which starts at 1540 and ends at 0340 presents a problem. For instance assume
the first note of the shift is the following 1540 "Start of shift
all present"
1700 ;jlakja;lkj;lkj
1600 lkjhlkjhlkjhlkjhljkh
0130 jkh;lkj;lkj;lkj;lkj;l
0200 ;lkasdjf;lkjsdf
When I sort using a Button with assigned macro, 1700 and 1600 are inverted
which is correct, However 0130 and 0200 are placed before 1540. I understand
the the program recognized that logically 0130 and 0200 come before 1540, but
for the purposes of my worksheet, I need 1540 to be recognized as the start
of time. Any solution would be greatly appreciated

Thank you

Brian

 

[Max]

One way ..

Try sorting on a helper col?

Assuming the mil. times are in col A
(Numbers formatted as Custom: Type: 0000),
comments in col B,
re-sample data in A1:B7 below

1540 Start of shift
1700 ;jlakja;lkj;lkj
1600 lkjhlkjhlkjhlkjhljkh
0130 jkh;lkj;lkj;lkj;lkj;l
0200 ;lkasdjf;lkjsdf
2359 hjhj
0330 tyyuu

(Note: 12 midnight would be 0000 in col A)

Put in C1:

=IF(A1=1540,0,IF(AND(A1>1540,A1<2400),A1-1540,A1+820))

Copy down

Now sort A1:C7 by col C > Ascending

You'll get the desired results in cols A and B:

1540 Start of shift
1600 lkjhlkjhlkjhlkjhljkh
1700 ;jlakja;lkj;lkj
2359 hjhj
0130 jkh;lkj;lkj;lkj;lkj;l
0200 ;lkasdjf;lkjsdf
0330 tyyuu

 

[Brian]

Thanks Max,
One more question,
how can I make the data in the C cells blank if there is no time in the A
cell?

Brian

 

[Max]

how can I make the data in the C cells blank
if there is no time in the A cell?

Try instead in C1:

=IF(A1="","",IF(A1=1540,0,IF(AND(A1>1540,A1<2400),A1-1540,A1+820)))

Copy down by a safe, max expected # of rows
to say, C100?
(can copy down ahead of input in col A)
--
And if you want an auto-sort on a new sheet,
try this play ..

(Assume the source data above is in Sheet1, cols A to C)

In a new sheet
--------------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)-1,COLUMN(A1)-1))

Copy across to B1, fill down by as many rows
as was done in col C in Sheet1, viz. down to B100

Format col A as Custom, Type: 0000

Cols A and B will return what's in cols A and B in Sheet1,
auto-sorted in ascending order from start of the shift at 1540

 

[Brian]

Very Awesome,
Thank You Max

Try instead in C1:

=IF(A1="","",IF(A1=1540,0,IF(AND(A1>1540,A1<2400),A1-1540,A1+820)))

Copy down by a safe, max expected # of rows
to say, C100?
(can copy down ahead of input in col A)
--
And if you want an auto-sort on a new sheet,
try this play ..

(Assume the source data above is in Sheet1, cols A to C)

In a new sheet
--------------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)-1,COLUMN(A1)-1))

Copy across to B1, fill down by as many rows
as was done in col C in Sheet1, viz. down to B100

Format col A as Custom, Type: 0000

Cols A and B will return what's in cols A and B in Sheet1,
auto-sorted in ascending order from start of the shift at 1540

 

[Max]

You're welcome, Brian !
Thanks for the feedback