Whisenhunt Enterprises is located in a metropolitan area. Denis Petrillo and Omar Vincent travel frequently for business. To help plan for flight delays, you will create a workbook that provides airline data with up-to-date arrival and departure information from the Tri-State Metropolitan Airport.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Download the text file exploring_e10_grader_h1.txt, the XML fileexploring_e10_grader_h1.xml, and the Excel file exploring_e10_grader_h1.xlsx. Open the workbook.
In the Airlines Codes sheet, click cell A2 and create a link to the tab-delimited text file you downloaded. Select the option that the text file has headers. Accept all other defaults.
Open Notepad, edit the e10_grader_h1.txt tab-delimited file. At the end of the last line, press ENTER, type Unified, press TAB, type UNA. Save and close the text file. In Excel, refresh the connection to the text file.
In the Departures sheet, in cell A1, create a Web query to the departures table on this Web page:http://media.pearsoncmg.com/ph/bp/bp_myitlab/departure_schedule_files/departure_schedule.htm. Maintain full HTML formatting before importing the table.
In the Filtered List sheet, create a table with headers using the range A1:E29.
In the Filtered List sheet, click cell A2. Insert a VLOOKUP function to lookup the airline in the Departures sheet, compare it to the airline table in the Airline Codes sheet, and return the code for that airline. For example, Unified will display as UNA. (The function should copy to the range A3:A29 automatically.)
In the Filtered List sheet, click cell B2. Use a text function to combine the airline code from the previous step with a space and the flight number on the respective row in the Departures sheet. (The function should copy to the range B3:B29 automatically.)
In the Filtered List sheet, click cell C2. Use a text function to display the cities from the Departures sheet where the first letter is capitalized but the remaining letters are lowercase, such as Houston. (The function should copy to the range C3:C29 automatically.)