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.

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

<tbody>
</tbody>

 

[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!!!