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

 

[Akuini]

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?.....

Ok, I'll amend the code.

 

[Akuini]

Try this, I added a part to sort by time.

Sub RandyD123_5()
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

'sort by 2 first letter
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("G"), 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

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

'sort by TIME
Set f = Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

    For Each c In f.Areas
            n = Split(c.Resize(, 2).Address, "$")(4) 'last row of areas
            For i = c.Row To n
            j = WorksheetFunction.CountIf(Range("G" & i & ":G" & n), Cells(i, "G")) 'case insensitive
                With Cells(i, "A").Resize(j, 11)
                        .sort Key1:=.Columns("E"), Order1:=xlAscending, Header:=xlNo  'sort by time
                End With
                i = i + j - 1
            Next
    Next

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

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

End Sub

 

[Akuini]

This one is a bit shorter:

Sub RandyD123_6()
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

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

n = Range("A" & Rows.Count).End(xlUp).Row
h = 4 'data start at row

'sort by 2 first letter
For i = n To h Step -1
    j = WorksheetFunction.CountIf(Range("A" & n & ":A" & h), Cells(i, "A")) 'case insensitive
        'sort by code then by time
        Cells(i - j + 1, "A").Resize(j, 11).sort Key1:=Columns("G"), Order1:=xlAscending, Key2:=Columns("E"), Order1:=xlAscending, Header:=xlNo
        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

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

End Sub

 

[RandyD123]

Thanks to you and bebo021999 for taking the time to help me out. I can't express my gratitude enough. It's funny how 2 people who know how to code can have slightly different vba and yet it produces the same end result. In the end I went with you because of the "sheet2" idea. That is exactly what I needed and got without asking. Regional airlines are funny because the same regional can operate for two different major carriers. A case in point is Republic Airlines, they operate for AA and UA out of the same airport (MHT). I can tell by the flight number who the major is and it's only two flights that are affected for this whole conversion thing. Again, thanks for all the help.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.