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

 

[Jeffrey Mahoney]

What code do you have? Have you tried recording a macro?

 

[Akuini]

See if it does what you want:

Sub RandyD123()

Dim c As Range, f As Range

Application.ScreenUpdating = False
'"A4" means start at A4
Set f = Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each c In f.Areas
'Debug.Print c.Address

    With c.Resize(, 5) 'expand to col A:E
        .Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlNo
    End With
Next
Application.ScreenUpdating = True

End Sub

but you probably doesn't like the result such as:
SW3459
SW536
this is because your data is alphanumeric, therefore it is sorted as text not number.
However, we can amend the code to also sort the number properly, but we need a temporary helper column, say col G.

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

 

[RandyD123]

As long as the end result has the flights alphabetically meaning all AA then DL then SP then SW then UA by time for each date section. Then end result should look like this....

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	AA5589	600	50	30
6	7/31/2023	AA6138	606	50	30
7	7/31/2023	AA5159	1236	50	30
8	7/31/2023	AA6040	1306	50	30
9	7/31/2023	AA5457	1350	50	30
10	7/31/2023	AA5917	1553	50	30
11	7/31/2023	AA5040	1716	50	30
12	7/31/2023	AA5021	1727	50	30
13	7/31/2023	AA5380	1915	50	30
14	7/31/2023	SP2044	700	50	30
15	7/31/2023	SW1913	540	50	30
16	7/31/2023	SW1915	630	50	30
17	7/31/2023	SW536	905	50	30
18	7/31/2023	SW3459	1210	50	30
19	7/31/2023	SW2648	1410	50	30
20	7/31/2023	SW3074	1505	50	30
21	7/31/2023	SW2370	1710	50	30
22	7/31/2023	SW181	1915	50	30
23	7/31/2023	UA3540	600	50	30
24	7/31/2023	UA4372	600	50	30
25	7/31/2023	UA3563	1249	50	30
26	7/31/2023	UA4591	1403	50	30
27
28	7/30/2023	AA5160	511	50	30
29	7/30/2023	AA5589	600	50	30
30	7/30/2023	AA6138	606	50	30
31	7/30/2023	AA5159	1236	50	30
32	7/30/2023	AA6040	1306	50	30
33	7/30/2023	AA5457	1350	50	30
34	7/30/2023	AA5917	1553	50	30
35	7/30/2023	AA5040	1716	50	30
36	7/30/2023	AA5021	1717	50	30
37	7/30/2023	AA5380	1915	50	30
38	7/30/2023	DL603	1000	50	30
39	7/30/2023	SP2044	700	50	30
40	7/30/2023	SP1159	1105	50	30
41	7/30/2023	SW1913	550	50	30
42	7/30/2023	SW2229	705	50	30
43	7/30/2023	SW3459	1025	50	30
44	7/30/2023	SW3629	1725	50	30
45	7/30/2023	SW952	1320	50	30
46	7/30/2023	UA3540	600	50	30
47	7/30/2023	UA4372	600	50	30
48	7/30/2023	UA3563	1249	50	30
49	7/30/2023	UA4591	1403	50	30
50
51	7/29/2023	AA5160	512	50	30
52	7/29/2023	AA5589	600	50	30
53	7/29/2023	AA6138	606	50	30
54	7/29/2023	AA3367	1236	50	30
55	7/29/2023	AA6040	1306	50	30
56	7/29/2023	AA4974	1403	50	30
57	7/29/2023	AA5917	1610	50	30
58	7/29/2023	AA5040	1716	50	30
59	7/29/2023	AA5380	1916	50	30
60	7/29/2023	SP2044	700	50	30
61	7/29/2023	SW2368	500	50	30
62	7/29/2023	SW1913	600	50	30
63	7/29/2023	SW1671	1150	50	30
64	7/29/2023	SW3459	1255	50	30
65	7/29/2023	SW952	1350	50	30
66	7/29/2023	SW2370	1625	50	30
67	7/29/2023	SW3300	1735	50	30
68	7/29/2023	SW1338	1850	50	30
69	7/29/2023	UA3540	600	50	30
70	7/29/2023	UA4372	600	50	30
71	7/29/2023	UA3563	1215	50	30
72
73			000
Desired Outpu

 

[RandyD123]

What code do you have? Have you tried recording a macro?

Sub Convert()
'
' Convert Macro
'

'
    Columns("A:K").Select
    Selection.ColumnWidth = 19.57
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B:B,C:C,D:D,H:H,J:J").Select
    Range("J1").Activate
    Selection.Delete shift:=xlToLeft
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("C:C").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert shift:=xlToRight
    Columns("B:B").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert shift:=xlToRight
    Range("B4").Select
    Cells.Replace What:="Comair Inc.", Replacement:="AA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Southwest Airlines Co.", Replacement:="SW", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Republic Airline Inc.", Replacement:="UA", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Spirit Air Lines", Replacement:="SP", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("B:B").ColumnWidth = 25.14
    Cells.Replace What:="Capital Cargo International", Replacement:="AA", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Cells.Replace What:="Envoy Inc.", Replacement:="AA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Skywest Airlines Inc.", Replacement:="AA", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Avelo Airlines", Replacement:="DL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("D:D").Select
    Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
    Range("D4").Select
    Selection.AutoFill Destination:=Range("D4:D75"), Type:=xlFillDefault
    Range("D4:D75").Select
    ActiveWindow.SmallScroll Down:=-48
    Range("D4:D75").Select
    Selection.Copy
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:C").Select
    Application.CutCopyMode = False
    Selection.Delete shift:=xlToLeft
    Columns("D:D").Select
    Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1], ""hmm"")"
    Range("D4").Select
    Selection.AutoFill Destination:=Range("D4:D75"), Type:=xlFillDefault
    Range("D4:D75").Select
    ActiveWindow.SmallScroll Down:=-33
    Range("D4:D75").Select
    Selection.Copy
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Delete shift:=xlToLeft
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Airline"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("B4").Select
    
'Code below will insert spaces between dates

Dim iRow As Integer, iCol As Integer
Dim oRng As Range

Set oRng = Range("A4")

iRow = oRng.Row
iCol = oRng.Column

Do
'
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
    Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown
    iRow = iRow + 2
Else
    iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""


'Code below will change the time column from text to a number format

With Range("c4:c" & Cells(Rows.Count, 1).End(xlUp).Row)
  .NumberFormat = "General"
  .Value = .Value
 End With
    
End Sub

 

[Akuini]

As long as the end result has the flights alphabetically meaning all AA then DL then SP then SW then UA by time for each date section. Then end result should look like this....

Try this one:

Sub RandyD123_2()

Dim c As Range, f As Range
Dim i As Long, j As Long
Dim va
Application.ScreenUpdating = False
'"A4" means start at A4
Set f = Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each c In f.Areas
    With c.Resize(, 5) 'expand to col A:E
        .Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlNo
    End With
Next

n = Range("B" & Rows.Count).End(xlUp).Row
va = Range("B1", Cells(Rows.Count, "B").End(xlUp))
va(3, 1) = "---"
For i = 4 To UBound(va, 1)
     j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While Left(va(i, 1), 2) = Left(va(i - 1, 1), 2)
    i = i - 1
    
    If i > j Then
        Range(Cells(j, "A"), Cells(i, "E")).Sort Key1:=Columns(3), Order1:=xlAscending, Header:=xlNo
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[RandyD123]

Try this one:

Sub RandyD123_2()

Dim c As Range, f As Range
Dim i As Long, j As Long
Dim va
Application.ScreenUpdating = False
'"A4" means start at A4
Set f = Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each c In f.Areas
    With c.Resize(, 5) 'expand to col A:E
        .Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlNo
    End With
Next

n = Range("B" & Rows.Count).End(xlUp).Row
va = Range("B1", Cells(Rows.Count, "B").End(xlUp))
va(3, 1) = "---"
For i = 4 To UBound(va, 1)
     j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While Left(va(i, 1), 2) = Left(va(i - 1, 1), 2)
    i = i - 1
   
    If i > j Then
        Range(Cells(j, "A"), Cells(i, "E")).Sort Key1:=Columns(3), Order1:=xlAscending, Header:=xlNo
    End If
Next
Application.ScreenUpdating = True
End Sub

That seems to work perfectly! Thank You!

 

[Akuini]

That seems to work perfectly! Thank You!

You're welcome, glad to help & thanks for the feedback.
Note: I am assuming that the airline codes always consist of 2 letters followed by numbers.

 

[RandyD123]

You're welcome, glad to help & thanks for the feedback.
Note: I am assuming that the airline codes always consist of 2 letters followed by numbers.

Yes that is correct. You can see in my vb code that I had to take the regional airline name and convert them to a 2 letter major carrier followed by flight number. The only issue I may have is that the time format was a real bear for me as I needed a number format and because the number of rows for each date could change on a daily basis, I had to extend the time format column, that's why the "000" at the end of the file. Wasn't sure how to resolve that....

 

[Akuini]

The only issue I may have is that the time format was a real bear for me as I needed a number format

'Code below will change the time column from text to a number format

With Range("c4:c" & Cells(Rows.Count, 1).End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value
End With

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?