Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting question
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 |
#2
|
|||
|
|||
One way ..
Try sorting on a helper col? Assuming the mil. times are in col A=20 (Numbers formatted as Custom: Type: 0000), comments in col B,=20 re-sample data in A1:B7 below=20 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: =3DIF(A1=3D1540,0,IF(AND(A11540,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 -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom ---- "Brian" wrote: in column A from 31 down to 51 military time will be=20 entered along with a=20 corresponding note in column B also from 31 down to 51. These times and notes are used to document occurences=20 during a work shift. and notes often entered out of order. I want to sort the=20 times at the end of=20 a work shiftso that they will be placed in order along=20 with notes to the=20 right. =20 PROBLEM: There must be two separate worksheets for two=20 different shifts. the first shift starts at 0340 and ends at 1540 no=20 problem when sorting=20 these times from the beggining of shift to its end,=20 HOWEVER, the second shift=20 which starts at 1540 and ends at 0340 presents a problem.=20 For instance assume=20 the first note of the shift is the following =20 1540 "Start of shift=20 all present"=20 =20 1700 ;jlakja;lkj;lkj 1600 =20 lkjhlkjhlkjhlkjhljkh 0130 =20 jkh;lkj;lkj;lkj;lkj;l =20 0200 ;lkasdjf;lkjsdf When I sort using a Button with assigned macro, 1700 and=20 1600 are inverted=20 which is correct, However 0130 and 0200 are placed before=20 1540. I understand=20 the the program recognized that logically 0130 and 0200=20 come before 1540, but=20 for the purposes of my worksheet, I need 1540 to be=20 recognized as the start=20 of time. Any solution would be greatly appreciated Thank you=20 Brian |
#3
|
|||
|
|||
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" wrote: 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(A11540,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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Brian" wrote: 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 |
#4
|
|||
|
|||
"Brian" wrote
.... 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(A11540,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)),Shee t1!$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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
Very Awesome,
Thank You Max "Max" wrote: "Brian" wrote .... 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(A11540,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)),Shee t1!$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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
You're welcome, Brian !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Brian" wrote Very Awesome, Thank You Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
Question on sorting dates | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) | |||
Sorting Data to Different Sheets | Excel Worksheet Functions |