Converting data from one tab to another

[RandyD123]

I know this is a big "ask", but I need some help. What I need to happen is all the data on the "raw data" sheet needs to be converted to the format on the "converted data" tab.


I can of course do this manually but I was looking to do this with a macro and run it from a button on the "raw data" tab.


This is what will happen:
1. Airline Name and Flight Number will convert to combine both of those columns and the result will be like shown on the "converted data" tab.
2. Equipment Name column will not be needed on the "converted data" tab.
3. The ETD time column will have to convert to just a number/text format.
4. The Day Of Week column will have to sort to the format shown on the "converted data" tab.
5. The conversion must produce the end result to be exactly as shown on the "converted data" tab, or at least as close as possible. Notice that all the info is sorted by day then by airline and then by time.


Any help would be much appreceiated. Thank you.....


https://1drv.ms/x/s!Ai35lp1419xzo4R8dmsgD0gET7HAsA

 

[Norie]

Why are there blank rows between the data on the 'Converted Data' tab?

Are they separating out the airlines?

 

[RandyD123]

yes, and it looks good for the end result!

 

[Norie]

Try this.

Option Explicit

Sub Experiment()
Dim ws As Worksheet
Dim rng As Range
Dim dicFlights As Object
Dim dicAirlines As Object
Dim strDay As String
Dim I As Long
Dim J As Long
Dim cnt As Long
Dim arrIn As Variant
Dim arrData As Variant
Dim ky As Variant

    arrIn = Sheets("Raw Data").Range("A1").CurrentRegion
    
    Set dicAirlines = CreateObject("Scripting.Dictionary")
    Set dicFlights = CreateObject("Scripting.Dictionary")
    
    For I = LBound(arrIn, 1) + 1 To UBound(arrIn, 1)
    
        ky = GetInitials(arrIn(I, 2))
        
        If Not dicAirlines.exists(ky) Then
            dicAirlines(ky) = arrIn(I, 2)
        End If
        
        ky = arrIn(I, 1) & "-" & GetInitials(arrIn(I, 2))
        
        If Not dicFlights.exists(ky) Then
        
            ReDim arrData(1 To 1, 1 To 4)
        
            arrData(1, 1) = GetInitials(arrIn(I, 2)) & arrIn(I, 3)
            arrData(1, 2) = arrIn(I, 4)
            arrData(1, 3) = Format(arrIn(I, 7), "hmm")
            arrData(1, 4) = arrIn(I, 6)
            
            dicFlights(ky) = arrData
        Else
            arrData = Application.Transpose(dicFlights(ky))
            cnt = UBound(arrData, 2)
            
            ReDim Preserve arrData(1 To 4, 1 To cnt + 1)
            
            arrData(1, cnt + 1) = GetInitials(arrIn(I, 2)) & arrIn(I, 3)
            arrData(2, cnt + 1) = arrIn(I, 4)
            arrData(3, cnt + 1) = Format(arrIn(I, 7), "hmm")
            arrData(4, cnt + 1) = arrIn(I, 6)
            
            dicFlights(ky) = Application.Transpose(arrData)
        End If
        
    Next I
    
    Set ws = Sheets.Add
    
    
    
    For I = 1 To 7
    
        Set rng = ws.Range("B2").Offset(, (I - 1) * 5)
        
        strDay = Format(I, "dddd")
        
        rng.Value = strDay
        
        Set rng = rng.Offset(2)
        
        For Each ky In dicAirlines.Keys
            arrData = dicFlights(strDay & "-" & ky)
            
            rng.Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData
                            
            Set rng = rng.Offset(UBound(arrData, 1) + 1)
        Next ky
    
    Next I
    
    
End Sub

Function GetInitials(strText As Variant) As String
Dim arrData As Variant
Dim I As Long
    arrData = Split(strText, " ")
    
    For I = LBound(arrData) To UBound(arrData)
        GetInitials = GetInitials & Left(arrData(I), 1)
    Next I
    
End Function

 

[sadboy309]

I did try make it with a button to Run Extract_filter macro.

Link down:

https://tinyurl.com/y3gwfe8p

 

[RandyD123]

Wow!! It's very close. Just a couple of minor things...The conversion for for the airlines should be converted as the two letter code as follows Southwest (SW) and United (UA) and Delta (DL). Currently it only converts American Airlines properly. Right now it is converting Delta as (DA) and Southwest as (SA) and United as (U).

And if possible AA should come first on the converted sheet, then Delta, then Southwest and lastly United.

Other than those two things I think it's PERFECT!!!! I am very grateful for the help so far!!! I am trying to understand the code so I could make those two changes myself but I can't see what I wold have to change.

I put that whole code into "thisworkbook"....maybe that was wrong?

 

[Norie]

The airline conversion is done by simply taking the initials of the airline - do you have some sort of lookup table for the 'real' codes?

AA does come first on the converted sheet, at least it does when I run the code.

 

[RandyD123]

I do not have a lookup table. I see how the conversion is working on the airlines..... Southwest Airlines (SA) Delta Airlines (DA) and United (U)......I'm just trying to figure out how to make them use SW, DL and UA. I'll run the code again and see if it sort with AA first...

The result of the conversion is here: https://1drv.ms/x/s!Ai35lp1419xzo4R8dmsgD0gET7HAsA

 

[RandyD123]

sorry for the "bad link"...the end result is here: https://1drv.ms/x/s!Ai35lp1419xzo4R_XBTnjE9wa51Ofg

 

[Norie]

What do you mean by the 'rest of the conversion'?

Do you mean the column widths, merged cells etc?