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

 

[RandyD123]

I think you may have misread that...."RESULTS" of the conversion, not "rest" of the conversion!!! SEE POST #9

 

[Norie]

Oops!!

 

[RandyD123]

So i have found a workaround for the naming of the airlines properly, not the best workaround but it works. But post #6 are the 2 concerns if solvable that would be awesome. Thanks for all the help so far!!!

 

[Norie]

Does the output need to be sorted alphabetically by airline code or is there some other sort order?

 

[RandyD123]

The sort order should be by Airline then by time. So AA would come first with all the flights listed by time, earliest first. See below. Also in the file that I uploaded there is an example of the layout on the "converted data11" tab.

[TABLE="width: 210"]
<tbody>[TR]
[TD]AA1729[/TD]
[TD]PHL[/TD]
[TD]600[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]AA4405[/TD]
[TD]DCA[/TD]
[TD]610[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]AA5474[/TD]
[TD]CLT[/TD]
[TD]615[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]AA3294[/TD]
[TD]ORD[/TD]
[TD]720[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]AA4825[/TD]
[TD]PHL[/TD]
[TD]803[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]AA5675[/TD]
[TD]CLT[/TD]
[TD]803[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]AA4923[/TD]
[TD]PHL[/TD]
[TD]1050[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]AA4434[/TD]
[TD]DCA[/TD]
[TD]1220[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]AA5632[/TD]
[TD]CLT[/TD]
[TD]1245[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]AA4792[/TD]
[TD]PHL[/TD]
[TD]1247[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]AA3291[/TD]
[TD]ORD[/TD]
[TD]1556[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]AA5595[/TD]
[TD]CLT[/TD]
[TD]1700[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]AA4841[/TD]
[TD]PHL[/TD]
[TD]1701[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]AA5061[/TD]
[TD]DCA[/TD]
[TD]1720[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]AA1904[/TD]
[TD]PHL[/TD]
[TD]1830[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DL2179[/TD]
[TD]ATL[/TD]
[TD]600[/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]DL3449[/TD]
[TD]LGA[/TD]
[TD]600[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]DL6182[/TD]
[TD]DTW[/TD]
[TD]700[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]DL6193[/TD]
[TD]DTW[/TD]
[TD]1235[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]DL6205[/TD]
[TD]DTW[/TD]
[TD]1645[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SW4110[/TD]
[TD]MDW[/TD]
[TD]605[/TD]
[TD]143[/TD]
[/TR]
[TR]
[TD]SW4104[/TD]
[TD]BWI[/TD]
[TD]610[/TD]
[TD]143[/TD]
[/TR]
</tbody>[/TABLE]

 

[RandyD123]

In this version of the file: https://1drv.ms/x/s!Ai35lp1419xzo4UDEX6nexi5KPJGYg you can see my workaround for renaming the airlines. Also notice the the "desired output" tab. When the script is run it creates a new tab called "sheet1". At this point it's only the sort order that needs a bit of tweaking. Not sure if there is a cleaner way to rename the airlines so they get renamed AA, DL, SW, UA.... Again, thank you for all the help on this one!!!