how to sort the data such that the end of the previous job is the start of the next job?

In excel, we have 2 columns start time and end time per job. how to sort the data such that the end of the previous job to start time of next job is sorted in ascending order?
Basically I am working with Truck Start Time of the Job and End Time of the Job. For better Route Planning/Scheduling I need to sort the data in the above mentioned manner.
From To Pickup Time Delivery time
A B 0200 0800
C D 0700 0800
0700 0900[INDENT]0700 1000[INDENT]0700 1000[INDENT]0800 1100[INDENT]0800 1100[INDENT]0800 1100[INDENT]0800 1800[INDENT]0800 1900[INDENT]0800 1900[INDENT]0800 2000[INDENT]0800 1830[INDENT]0800 1830[INDENT]0800 1830[INDENT]0900 1100[INDENT]0900 1200[INDENT]0900 1200[INDENT]0900 1300[INDENT]0900 1300[INDENT]1100 1400[INDENT]1100 1400[INDENT]1100 1400[INDENT]1100 1400[INDENT]1100 1400[INDENT]1100 1400[INDENT]1200 1600

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi John Topley,

Thanks for your prompt response.

As requested, attached the sample data for sorting.

Before Sheet:

I have Job No in column D, Sub Job No which indicates the No of Truck Trips in Column E, From and To in Column H and I.
Estimated Pickup time in column J and Estimated Delivery Time in column K. The From and To Customers are classified into zones.

After Sheet:

As the end state, it is best to sort the end of 1st job equals or greater than the start of the second job timing and same zone.
If Same Zone is not available, different zone is also acceptable but need to have at least 1 hr buffer time to travel.
I want to use this logic for route scheduling for the future jobs.

Hi kvsrinivasamurthy,

Thanks for your response.
yes you are right in the West-West Zone, S.No 47 should be on the 2nd row and S.No 2 should be on 3rd row.
While doing manual simulation,I copied S.No 47 in two rows (Row 19th and Row 25th) by mistake.
One should be removed and the other one need to be moved to the 2nd row.
Thanks for the clarification.

Hi Kvsrinivasamurthy,

Thanks for the support.

It worked but it did not reflect the exact after sheet.
The timing matched but practically when it is a different zone, same timing for the next job will not be possible.
Seems like cannot do it without a macro or a SQL logic in the backend

Hi kvsrinivasamurthy,

Please refer to the attached file tab before(2), after the sorting is performed as you had helped me to do so.

Those cells highlighted in yellow would be the required outcome.

Basically to Sort by Zone,Pickup timing
same zone to group all those into 1 truck's full day's job using the job requests from the customer.
If different zone (1st Job in east, next job available is in the West, at least to have 1 hour interval)
Hope this helps.

I re-save with Excel 97-2003 version.
Attached the picture as well for the highlighted cells.

Thank you soo much.. Macro works

Hi kvsrinivasamurthy,

If I try the same for a larger data range. It seems to create individual Trips for each row.
It works well with the 1 day basis.
I have not done any macro coding before. I just know the basics of it.
Please check if the macro logic needs to be amended for a larger data range.
Thanks for the support.

Hi
Trip wise please

Thank you very much. The code is working.

Hi,

Could you please explain the below queries?

LRpage = Range("A" & Rows.Count).End(xlUp).Row (is used for selection of the first row till the end of the sheet?)
Sheets("Before").Copy Before:=Sheets(1) (Creating the output in the tab before the actual sheet)

Range("N1") = "Pickup Time1" (Creating Row Header with name Pickup Time1)
Range("O1") = "Delivery time1 " (Creating Row Header with name Delivery time1)

Range("N2:O" & LRpage).Formula = "=LEFT(J2,2)& "":""&RIGHT(J2,2)" (Changing text to time)

ActiveSheet.Sort.SortFields.Clear (Adding the Sorting Option and Clearing the Sorting that is currently existing)
ActiveSheet.Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers (Please explain this why is range B1 is used? I understood it is sorting by Ascending Order, the data that is sorted similar to Advanced Sort Option in the excel. Is the Sorting done for the column Pickup Date1?)
ActiveSheet.Sort.SortFields.Add Key:=Range("N1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers (Please explain this why is range N1 is used? Is the Sorting done for the Delivery time1 here?)

With ActiveSheet.Sort
.SetRange Range("A2:O" & LRpage)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply ( With Function is used. But why Range ("A2:O" & LRPage), xlPinYin is a sorting method used for?)
End With

Range("A1:O1").Cut Range("R1") (Please explain the process that is performed here)

X = 1 (Why X is declared to be 1, SR declared to be 2 and LR declared to be 1)
SR = 2
LR = 1

Do While WorksheetFunction.CountA(Range("A2:A" & LRpage)) <> 0 (Do While Loop Function is used to perform the Trip Count until the Rows are not equal to null)

TripNo = 0 (TripNo to be started with 0)
LR = LR + WorksheetFunction.CountIf(Range("B" & SR & ":B" & LRpage), Range("B" & SR)) (Please explain the process involved in this code)

Do While WorksheetFunction.CountA(Range("A" & SR & ":A" & LR)) <> 0 (Please explain the process involved in this code)
StNo = 0 (What is StNo used for)
TripNo = TripNo + 1 (If the Condition satisfies it adds to the Trip No)

For T = 2 To LR (LR is the Variable to store the count of last row, For Loop is performed till the last row but why start with 2?)

If Range("A" & T) <> "" Then (If Then Decision Loop is used, but why "A" & T) not equal to null

If StNo = 0 Then
X = X + 1 (Please explain the process involved in this code)
Range("A" & T & ":O" & T).Cut Range("R" & X) (Please explain the process involved in this code)
StNo = 1 (Please explain the process involved in this code)
Range("AG" & X) = "Trip " & TripNo (Please explain the process involved in this code)
Else

If TimeValue(Range("N" & T).Value) >= TimeValue(Range("AF" & X).Value) + TimeValue("1:00:00") _ (Please explain the process involved in this code)
And UCase(Range("L" & T).Value) = UCase(Range("AD" & X).Value) Then (Please explain the process involved in this code)
X = X + 1 (Please explain the process involved in this code)
Range("A" & T & ":O" & T).Cut Range("R" & X) (Please explain the process involved in this code)
Range("AG" & X) = "Trip " & TripNo (Please explain the process involved in this code)
End If

End If

End If

Next T (Please explain the process involved in this code)

Loop

SR = LR + 1 (Please explain the process involved in this code)
Loop

Range("A1:Q1").EntireColumn.Delete (why is the A1:Q1 is deleted?)
Columns("A:P").EntireColumn.AutoFit (Auto fitting of Columns for columns A to P)

Application.ScreenUpdating = True (For Faster Macro execution and not displaying the process that is performed)

End Sub

Thanks in Advance.

Noted with thanks

Hi,

No worries.
Thank you very much.
The code is much faster than the previous one.
Please find attached my queries for the new code.
In the future for the same excel if there is a need to add another sorting field how to do so?

got it.. Thanks for the clarification