Sorting with VBA

[RandyD123]

Trying to sort this using VBA to have airline alphabetically then by time for each date. I've made it this far but can't figure the rest... I need to keep the spaces between the dates. Any help would be appreceiated. Thanks!

PIMS Flight Converter.xlsm
	A	B	C	D	E
1	Airport Total by Departure Time-Flight#-Airline
2
3	Date	Airline	Time	PAX	PCPAX
4	7/31/2023	AA5160	512	50	30
5	7/31/2023	SW1913	540	50	30
6	7/31/2023	UA3540	600	50	30
7	7/31/2023	UA4372	600	50	30
8	7/31/2023	AA5589	600	50	30
9	7/31/2023	AA6138	606	50	30
10	7/31/2023	SW1915	630	50	30
11	7/31/2023	SP2044	700	50	30
12	7/31/2023	SW536	905	50	30
13	7/31/2023	SW3459	1210	50	30
14	7/31/2023	AA5159	1236	50	30
15	7/31/2023	UA3563	1249	50	30
16	7/31/2023	AA6040	1306	50	30
17	7/31/2023	AA5457	1350	50	30
18	7/31/2023	UA4591	1403	50	30
19	7/31/2023	SW2648	1410	50	30
20	7/31/2023	SW3074	1505	50	30
21	7/31/2023	AA5917	1553	50	30
22	7/31/2023	SW2370	1710	50	30
23	7/31/2023	AA5040	1716	50	30
24	7/31/2023	AA5021	1727	50	30
25	7/31/2023	SW181	1915	50	30
26	7/31/2023	AA5380	1915	50	30
27
28	7/30/2023	AA5160	511	50	30
29	7/30/2023	SW1913	550	50	30
30	7/30/2023	UA3540	600	50	30
31	7/30/2023	UA4372	600	50	30
32	7/30/2023	AA5589	600	50	30
33	7/30/2023	AA6138	606	50	30
34	7/30/2023	SP2044	700	50	30
35	7/30/2023	SW2229	705	50	30
36	7/30/2023	DL603	1000	50	30
37	7/30/2023	SW3459	1025	50	30
38	7/30/2023	SP1159	1105	50	30
39	7/30/2023	AA5159	1236	50	30
40	7/30/2023	UA3563	1249	50	30
41	7/30/2023	AA6040	1306	50	30
42	7/30/2023	SW952	1320	50	30
43	7/30/2023	AA5457	1350	50	30
44	7/30/2023	UA4591	1403	50	30
45	7/30/2023	AA5917	1553	50	30
46	7/30/2023	AA5040	1716	50	30
47	7/30/2023	AA5021	1717	50	30
48	7/30/2023	SW3629	1725	50	30
49	7/30/2023	AA5380	1915	50	30
50
51	7/29/2023	SW2368	500	50	30
52	7/29/2023	AA5160	512	50	30
53	7/29/2023	SW1913	600	50	30
54	7/29/2023	UA3540	600	50	30
55	7/29/2023	UA4372	600	50	30
56	7/29/2023	AA5589	600	50	30
57	7/29/2023	AA6138	606	50	30
58	7/29/2023	SP2044	700	50	30
59	7/29/2023	SW1671	1150	50	30
60	7/29/2023	UA3563	1215	50	30
61	7/29/2023	AA3367	1236	50	30
62	7/29/2023	SW3459	1255	50	30
63	7/29/2023	AA6040	1306	50	30
64	7/29/2023	SW952	1350	50	30
65	7/29/2023	AA4974	1403	50	30
66	7/29/2023	AA5917	1610	50	30
67	7/29/2023	SW2370	1625	50	30
68	7/29/2023	AA5040	1716	50	30
69	7/29/2023	SW3300	1735	50	30
70	7/29/2023	SW1338	1850	50	30
71	7/29/2023	AA5380	1916	50	30
72
73			000
74			000
75			000
76			000
77			000
Converted Sheet

 

[RandyD123]

So, originally the time is in text format instead of time format?
How did the data load into the sheet? from a text file?
Can you post data before you run the Sub Convert?

Here is a link to the raw data file If you decide to look at it, the time format has to be a number format in the end. I had to first convert it (=TEXT(C4, "hmm").....maybe not exact cell but that's the formula I had to use. Then I had to change it back to a number format. Anyway the raw data file simply gets copied into the Convert file, then I run the macros to get the end result. For the most part it was just moving, deleting, and combining columns and formatting out the raw data file. The time thing was a nightmare for me. That was the only column that I had problems with and hence the "000" at the end. The raw data file could have more rows on any given day, but the columns would never change. I posted my vba that got me to where you stepped in.

 

[Akuini]

the time format has to be a number format in the end.

I don't see why. Do you need it in number format in order to sort data properly? Have you tried sort data by time in your raw file? When I tried it, the data sorted properly and the dates (col A) are still in order.
If you want, I probably could amend your code to make it more concise and efficient.

 

[RandyD123]

I don't see why. Do you need it in number format in order to sort data properly? Have you tried sort data by time in your raw file? When I tried it, the data sorted properly and the dates (col A) are still in order.
If you want, I probably could amend your code to make it more concise and efficient.

Believe it or not the converted data gets copied and pasted into another file where the time format must be in a number format or the error checking code flags the whole thing. And yes, if you don't mind I would love to see you amend the code to a more efficient way. Just remember, the rows can increase or decrease on any given day. We get 3 days worth of data everyday. Again the end result must me airline first, sorted by time after with a space between each day. Exactly the way it did with your original help.

 

[bebo021999]

So, with the Raw data, the results are in sheet Sort

Option Explicit
Sub sort()
Dim lr&, i&, j&, rng, air, air1, dat, u As Range
Dim arr()
air1 = Array("Com", "Cap", "Env", "Sky", "Sou", "Rep", "Spi", "Ave") ' first 3 letters of Airlines. Add more if needed
air = Array("AA", "AA", "AA", "AA", "SW", "UA", "SP", "DL") ' corresponding letters of Airlines. Add more if needed

'store Raw into array "rng"
With Sheets("Raw Data")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A4:K" & lr).Value2
End With

'generate sheet output "Sort", overwright old sheet
On Error Resume Next
Sheets("Sort").Delete
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sort"
On Error GoTo 0

ReDim arr(1 To UBound(rng), 1 To 6)
For i = 1 To UBound(rng)
    arr(i, 1) = rng(i, 1): arr(i, 3) = rng(i, 5)
    arr(i, 4) = rng(i, 9): arr(i, 5) = rng(i, 11)
    For j = 0 To UBound(air1)
            If rng(i, 7) Like air1(j) & "*" Then
                arr(i, 2) = air(j) & rng(i, 6)
                Exit For
            End If
    Next
    arr(i, 6) = (100000 - arr(i, 1)) & Left(arr(i, 2), 2) & rng(i, 5) ' 100000-date: convert date into Z-A
Next

'paste array back to sheet, then sort column F (helper column, contain info like: 54862AA0.22)
'For example: 31-Jul, AA5160, 5:12AM. With 31-Jul=45138 then 100000-45138 = 54862. 5:12AM=0.22
'==> cell F4 = 54862AA0.22. then sort column F
Range("A1").Value = "Airport Total by Departure Time-Flight#-Airline"
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
With Range("A4").Resize(UBound(arr), 6)
    .Value = arr
    .sort key1:=Range("F3")
End With
Columns("F").Delete
Columns("A").NumberFormat = "mm/dd/yyyy"

'loop column A, search for rows where value change, then insert
lr = Cells(Rows.Count, "A").End(xlUp).Row
dat = Cells(4, 1).Value
For i = 5 To lr
    If Cells(i, 1).Value <> dat Then
        If u Is Nothing Then
            Set u = Cells(i, 1)
        Else
            Set u = Union(u, Cells(i, 1))
        End If
    End If
    dat = Cells(i, 1).Value
Next
u.EntireRow.Insert
Range("A3").Select
End Sub

Raw Data Sheet.xlsm

drive.google.com

 

[RandyD123]

So, with the Raw data, the results are in sheet Sort

Option Explicit
Sub sort()
Dim lr&, i&, j&, rng, air, air1, dat, u As Range
Dim arr()
air1 = Array("Com", "Cap", "Env", "Sky", "Sou", "Rep", "Spi", "Ave") ' first 3 letters of Airlines. Add more if needed
air = Array("AA", "AA", "AA", "AA", "SW", "UA", "SP", "DL") ' corresponding letters of Airlines. Add more if needed

'store Raw into array "rng"
With Sheets("Raw Data")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A4:K" & lr).Value2
End With

'generate sheet output "Sort", overwright old sheet
On Error Resume Next
Sheets("Sort").Delete
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sort"
On Error GoTo 0

ReDim arr(1 To UBound(rng), 1 To 6)
For i = 1 To UBound(rng)
    arr(i, 1) = rng(i, 1): arr(i, 3) = rng(i, 5)
    arr(i, 4) = rng(i, 9): arr(i, 5) = rng(i, 11)
    For j = 0 To UBound(air1)
            If rng(i, 7) Like air1(j) & "*" Then
                arr(i, 2) = air(j) & rng(i, 6)
                Exit For
            End If
    Next
    arr(i, 6) = (100000 - arr(i, 1)) & Left(arr(i, 2), 2) & rng(i, 5) ' 100000-date: convert date into Z-A
Next

'paste array back to sheet, then sort column F (helper column, contain info like: 54862AA0.22)
'For example: 31-Jul, AA5160, 5:12AM. With 31-Jul=45138 then 100000-45138 = 54862. 5:12AM=0.22
'==> cell F4 = 54862AA0.22. then sort column F
Range("A1").Value = "Airport Total by Departure Time-Flight#-Airline"
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
With Range("A4").Resize(UBound(arr), 6)
    .Value = arr
    .sort key1:=Range("F3")
End With
Columns("F").Delete
Columns("A").NumberFormat = "mm/dd/yyyy"

'loop column A, search for rows where value change, then insert
lr = Cells(Rows.Count, "A").End(xlUp).Row
dat = Cells(4, 1).Value
For i = 5 To lr
    If Cells(i, 1).Value <> dat Then
        If u Is Nothing Then
            Set u = Cells(i, 1)
        Else
            Set u = Union(u, Cells(i, 1))
        End If
    End If
    dat = Cells(i, 1).Value
Next
u.EntireRow.Insert
Range("A3").Select
End Sub

Raw Data Sheet.xlsm

drive.google.com

Looks good, except the "Time" column. That column has to be 3 or 4 digits and in a number format. The "Date" column can be in any format.

 

[bebo021999]

try replace

arr(i, 3) = rng(i, 5)

with

arr(i, 3) = Hour(rng(i, 5)) * 100 + Minute(rng(i, 5))

Option Explicit
Sub sort()
Dim lr&, i&, j&, rng, air, air1, dat, u As Range
Dim arr()
air1 = Array("Com", "Cap", "Env", "Sky", "Sou", "Rep", "Spi", "Ave") ' first 3 letters of Airlines. Add more if needed
air = Array("AA", "AA", "AA", "AA", "SW", "UA", "SP", "DL") ' corresponding letters of Airlines. Add more if needed

'store Raw into array "rng"
With Sheets("Raw Data")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A4:K" & lr).Value2
End With

'generate sheet output "Sort", overwright old sheet
On Error Resume Next
Sheets("Sort").Delete
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sort"
On Error GoTo 0

ReDim arr(1 To UBound(rng), 1 To 6)
For i = 1 To UBound(rng)

'New adjust-----------
    arr(i, 1) = rng(i, 1): arr(i, 3) = Hour(rng(i, 5)) * 100 + Minute(rng(i, 5))
'-----------------
    
    arr(i, 4) = rng(i, 9): arr(i, 5) = rng(i, 11)
    For j = 0 To UBound(air1)
            If rng(i, 7) Like air1(j) & "*" Then
                arr(i, 2) = air(j) & rng(i, 6)
                Exit For
            End If
    Next
    arr(i, 6) = (100000 - arr(i, 1)) & Left(arr(i, 2), 2) & rng(i, 5) ' 100000-date: convert date into Z-A
Next

'paste array back to sheet, then sort column F (helper column, contain info like: 54862AA0.22)
'For example: 31-Jul, AA5160, 5:12AM. With 31-Jul=45138 then 100000-45138 = 54862. 5:12AM=0.22
'==> cell F4 = 54862AA0.22. then sort column F
Range("A1").Value = "Airport Total by Departure Time-Flight#-Airline"
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
With Range("A4").Resize(UBound(arr), 6)
    .Value = arr
    .sort key1:=Range("F3")
End With
Columns("F").Delete
Columns("A").NumberFormat = "mm/dd/yyyy"

'loop column A, search for rows where value change, then insert
lr = Cells(Rows.Count, "A").End(xlUp).Row
dat = Cells(4, 1).Value
For i = 5 To lr
    If Cells(i, 1).Value <> dat Then
        If u Is Nothing Then
            Set u = Cells(i, 1)
        Else
            Set u = Union(u, Cells(i, 1))
        End If
    End If
    dat = Cells(i, 1).Value
Next
u.EntireRow.Insert
Range("A3").Select
End Sub

 

[Akuini]

@RandyD123
First, I put Airline name & the code in sheet2, so it's not hardcoded, it will make it easier to update.

RandyD123 - Raw Data Sheet 1.xlsm
	A	B
1	AIRLINE	CODE
2	Avelo Airlines	DL
3	Capital Cargo International	AA
4	Comair Inc.	AA
5	Envoy Inc.	AA
6	Republic Airline Inc.	UA
7	Skywest Airlines Inc.	AA
8	Southwest Airlines Co.	SW
9	Spirit Air Lines	SP
10
11
Sheet2

Second, I read the data, I see that time is already sort ascending for each Airline, so we don't need to sort it again. Is that always the case? If not, then we need to amend the code.

This will transform data from Raw sheet to end result (in sheet Result), so you don't need my previous code & your original code.
@bebo021999, it's a good idea to create a new sheet to send the results, so I borrowed that part of your code. I hope you don't mind.

Sub RandyD123_4()
Dim c As Range
Application.ScreenUpdating = False

'generate sheet output "Result", overwright old sheet
On Error Resume Next
    Sheets("Result").Delete
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Result"
    Sheets("Raw Data").Cells.Copy Range("A1")
On Error GoTo 0

With Sheets("Sheet2")  'replace Airline with 2 letters
    For Each c In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        Range("G:G").Replace What:=c.Value, Replacement:=c.Offset(, 1), LookAt:=xlWhole, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End With

n = Range("A" & Rows.Count).End(xlUp).Row
h = 4 'data start at row
For i = n To h Step -1
    j = WorksheetFunction.CountIf(Range("A" & n & ":A" & h), Cells(i, "A")) 'case insensitive
'        Debug.Print Cells(i - j + 1, "A").Resize(j, 11).Address
        Cells(i - j + 1, "A").Resize(j, 11).sort Key1:=Columns(7), Order1:=xlAscending, Header:=xlNo  'sort by 2 first letter
        If i - j + 1 <> h Then
            Rows(i - j + 1).Insert  'insert row between different dates
        End If
        i = i - j + 1
Next

'combine code & flight number
With Range("B4", Range("B" & Rows.Count).End(xlUp))
   .Value = Evaluate(.Columns(6).Address & "&" & .Columns(5).Address)
End With

'TIME to number format
For Each c In Range("E4", Cells(Rows.Count, "E").End(xlUp))
    c = Replace(Left(c.Text, 6), ":", "")
Next

Range("C:D,F:H,J:J").Delete
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
Application.ScreenUpdating = True

End Sub

 

[RandyD123]

try replace

arr(i, 3) = rng(i, 5)

with

arr(i, 3) = Hour(rng(i, 5)) * 100 + Minute(rng(i, 5))

Option Explicit
Sub sort()
Dim lr&, i&, j&, rng, air, air1, dat, u As Range
Dim arr()
air1 = Array("Com", "Cap", "Env", "Sky", "Sou", "Rep", "Spi", "Ave") ' first 3 letters of Airlines. Add more if needed
air = Array("AA", "AA", "AA", "AA", "SW", "UA", "SP", "DL") ' corresponding letters of Airlines. Add more if needed

'store Raw into array "rng"
With Sheets("Raw Data")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A4:K" & lr).Value2
End With

'generate sheet output "Sort", overwright old sheet
On Error Resume Next
Sheets("Sort").Delete
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sort"
On Error GoTo 0

ReDim arr(1 To UBound(rng), 1 To 6)
For i = 1 To UBound(rng)

'New adjust-----------
    arr(i, 1) = rng(i, 1): arr(i, 3) = Hour(rng(i, 5)) * 100 + Minute(rng(i, 5))
'-----------------
   
    arr(i, 4) = rng(i, 9): arr(i, 5) = rng(i, 11)
    For j = 0 To UBound(air1)
            If rng(i, 7) Like air1(j) & "*" Then
                arr(i, 2) = air(j) & rng(i, 6)
                Exit For
            End If
    Next
    arr(i, 6) = (100000 - arr(i, 1)) & Left(arr(i, 2), 2) & rng(i, 5) ' 100000-date: convert date into Z-A
Next

'paste array back to sheet, then sort column F (helper column, contain info like: 54862AA0.22)
'For example: 31-Jul, AA5160, 5:12AM. With 31-Jul=45138 then 100000-45138 = 54862. 5:12AM=0.22
'==> cell F4 = 54862AA0.22. then sort column F
Range("A1").Value = "Airport Total by Departure Time-Flight#-Airline"
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
With Range("A4").Resize(UBound(arr), 6)
    .Value = arr
    .sort key1:=Range("F3")
End With
Columns("F").Delete
Columns("A").NumberFormat = "mm/dd/yyyy"

'loop column A, search for rows where value change, then insert
lr = Cells(Rows.Count, "A").End(xlUp).Row
dat = Cells(4, 1).Value
For i = 5 To lr
    If Cells(i, 1).Value <> dat Then
        If u Is Nothing Then
            Set u = Cells(i, 1)
        Else
            Set u = Union(u, Cells(i, 1))
        End If
    End If
    dat = Cells(i, 1).Value
Next
u.EntireRow.Insert
Range("A3").Select
End Sub

Yes, that works perfectly.

 

[RandyD123]

@RandyD123
First, I put Airline name & the code in sheet2, so it's not hardcoded, it will make it easier to update.

RandyD123 - Raw Data Sheet 1.xlsm
	A	B
1	AIRLINE	CODE
2	Avelo Airlines	DL
3	Capital Cargo International	AA
4	Comair Inc.	AA
5	Envoy Inc.	AA
6	Republic Airline Inc.	UA
7	Skywest Airlines Inc.	AA
8	Southwest Airlines Co.	SW
9	Spirit Air Lines	SP
10
11
Sheet2

Second, I read the data, I see that time is already sort ascending for each Airline, so we don't need to sort it again. Is that always the case? If not, then we need to amend the code.

This will transform data from Raw sheet to end result (in sheet Result), so you don't need my previous code & your original code.
@bebo021999, it's a good idea to create a new sheet to send the results, so I borrowed that part of your code. I hope you don't mind.

Sub RandyD123_4()
Dim c As Range
Application.ScreenUpdating = False

'generate sheet output "Result", overwright old sheet
On Error Resume Next
    Sheets("Result").Delete
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Result"
    Sheets("Raw Data").Cells.Copy Range("A1")
On Error GoTo 0

With Sheets("Sheet2")  'replace Airline with 2 letters
    For Each c In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        Range("G:G").Replace What:=c.Value, Replacement:=c.Offset(, 1), LookAt:=xlWhole, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End With

n = Range("A" & Rows.Count).End(xlUp).Row
h = 4 'data start at row
For i = n To h Step -1
    j = WorksheetFunction.CountIf(Range("A" & n & ":A" & h), Cells(i, "A")) 'case insensitive
'        Debug.Print Cells(i - j + 1, "A").Resize(j, 11).Address
        Cells(i - j + 1, "A").Resize(j, 11).sort Key1:=Columns(7), Order1:=xlAscending, Header:=xlNo  'sort by 2 first letter
        If i - j + 1 <> h Then
            Rows(i - j + 1).Insert  'insert row between different dates
        End If
        i = i - j + 1
Next

'combine code & flight number
With Range("B4", Range("B" & Rows.Count).End(xlUp))
   .Value = Evaluate(.Columns(6).Address & "&" & .Columns(5).Address)
End With

'TIME to number format
For Each c In Range("E4", Cells(Rows.Count, "E").End(xlUp))
    c = Replace(Left(c.Text, 6), ":", "")
Next

Range("C:D,F:H,J:J").Delete
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
Application.ScreenUpdating = True

End Sub

Yes that works perfectly. I like that there is a separate sheet to add Airlines. Regional airlines can change for a specific carrier. Thank you.

 

[RandyD123]

@RandyD123
First, I put Airline name & the code in sheet2, so it's not hardcoded, it will make it easier to update.

RandyD123 - Raw Data Sheet 1.xlsm
	A	B
1	AIRLINE	CODE
2	Avelo Airlines	DL
3	Capital Cargo International	AA
4	Comair Inc.	AA
5	Envoy Inc.	AA
6	Republic Airline Inc.	UA
7	Skywest Airlines Inc.	AA
8	Southwest Airlines Co.	SW
9	Spirit Air Lines	SP
10
11
Sheet2

Second, I read the data, I see that time is already sort ascending for each Airline, so we don't need to sort it again. Is that always the case? If not, then we need to amend the code.

This will transform data from Raw sheet to end result (in sheet Result), so you don't need my previous code & your original code.
@bebo021999, it's a good idea to create a new sheet to send the results, so I borrowed that part of your code. I hope you don't mind.

Sub RandyD123_4()
Dim c As Range
Application.ScreenUpdating = False

'generate sheet output "Result", overwright old sheet
On Error Resume Next
    Sheets("Result").Delete
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Result"
    Sheets("Raw Data").Cells.Copy Range("A1")
On Error GoTo 0

With Sheets("Sheet2")  'replace Airline with 2 letters
    For Each c In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        Range("G:G").Replace What:=c.Value, Replacement:=c.Offset(, 1), LookAt:=xlWhole, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End With

n = Range("A" & Rows.Count).End(xlUp).Row
h = 4 'data start at row
For i = n To h Step -1
    j = WorksheetFunction.CountIf(Range("A" & n & ":A" & h), Cells(i, "A")) 'case insensitive
'        Debug.Print Cells(i - j + 1, "A").Resize(j, 11).Address
        Cells(i - j + 1, "A").Resize(j, 11).sort Key1:=Columns(7), Order1:=xlAscending, Header:=xlNo  'sort by 2 first letter
        If i - j + 1 <> h Then
            Rows(i - j + 1).Insert  'insert row between different dates
        End If
        i = i - j + 1
Next

'combine code & flight number
With Range("B4", Range("B" & Rows.Count).End(xlUp))
   .Value = Evaluate(.Columns(6).Address & "&" & .Columns(5).Address)
End With

'TIME to number format
For Each c In Range("E4", Cells(Rows.Count, "E").End(xlUp))
    c = Replace(Left(c.Text, 6), ":", "")
Next

Range("C:D,F:H,J:J").Delete
Range("A3:E3").Value = Array("Date", "Airline", "Time", "PAX", "PCPAX")
Application.ScreenUpdating = True

End Sub

As far as the Airlines already being sorted by time by default, that is an unknow at this time. Maybe a snippet somewhere just in case the default is not always true?.....