Please help me solve 3-40 on excel with the 4 bullet points. Last_Name First_Nam
ID: 3888619 • Letter: P
Question
Please help me solve 3-40 on excel with the 4 bullet points.
Last_Name First_Name Phone_Number Frequent_Flier_Number Miles Flown Easton Dennis 208-882-3476 725722 Lin Yu-Lin 208-882-3232 530376 Beasley Steve 509-332-2354 144323 Becker Hubert 509-336-6578 603151 Fuhrmann Leslie 509-336-9807 752937 Gillespie Patricia 509-332-4578 803686 Li Chun 509-357-4433 211641 Lee SooJin 509-332-6589 740617 Lawrence Earl 509-123-4561 813258 Baker Beth 509-555-8947 219608 Zhan Man 509-332-4567 978445 Yang Jung 509-357-4509 515086 Hutchinson Eric 509-336-5465 867814 Thompson Caitlin 509-332-8790 15782 Baldwin MaryAnn 208-882-4455 206584 Dumont Mark 509-332-4526 74826 Gibson Fernanda 208-882-6745 79235 Sanderson Sonja 509-336-5567 380392 Clausen Gina 509-336-7777 43476 Hunter Anna 208-882-3424 189143 Marshal Debbie 208-882-1525 788839 Austin Joseph 509-333-4576 398846 Clark Heather 509-332-9087 276924 Thornton Janet 509-332-8679 883456 Norton Cynthia 509-357-5676 634054 Walker Ken 509-332-6709 259804 Nauman Michael 509-357-5673 742971 Boyd Gloria 509-332-1234 925803 Proctor Ginny 208-875-3333 525630 Hunt Jack 509-332-6708 424960 West Helen 509-357-3487 747197 Henderson Angela 509-332-4343 434996 Stewart Kathy 509-357-8889 306513 Cox Linda 509-332-7456 472458 Choi Diane 208-882-5478 224029 Looney Carl 509-222-5645 401419 Fujimoto Carla 509-332-4532 542880 Herman Frank 509-332-1267 837399 Wallace Alex 208-882-5576 304136 Hall Marion 509-357-3434 135797 Weber Cliff 208-875-7844 874563 Frazier Nancy 509-332-1243 294736 Sanchez Ramon 208-882-9898 818401 Note: The existing dala book's website: www.pearsonhighered preadsheet Application: Tracking Frequent-Flier Mileage dif tra tir tl 3-40. You Campus Travel. In your first meeting. perations manager learned that you are taking an ave recently landed a part-time job as a busi- ness analyst for uctory MIS class. As the manager is not very cient in using office software tools, he is doing introd nt-flier mileage in two separate Excel work- . One is the customer's contact information, and iles flown. Being familiar with the of spreadsheet applications, you suggest one workbook to handle both functions. To the second is the m setting up complete this, you must do the following: Open the spreadsheet frequentflier2.csv. You w see a tab for customers and a tab labeled "miles ill flown." Use the vlookup function to enter the miles flown column by looking up the frequent-flier number (Hint: If done correctly with absolute references, you should be able to enter the vlookup formula in the first cell in the "miles flown'" column and copy Use conditional formatting to highlight all frequent it down for all the cells.) fliers who have less than 4,000 total miles. Finally, sort the frequent fliers by total miles in descending order.Explanation / Answer
Created an Excel File keeping first Blank cell under sheet1 with following cloumns
First_Name
(Column B)
Frequent_Flier_Number
(Column D)
Miles Flown
(Column E) Blank
Also created a Tab "Miles Flown" keeping two columns :
I have copied the frequent flyer no. from the original tab . Frequent Flyer No. will be the common key or field for VLOOKUP to run.
Step 1: Enter The Formula at E2 : =VLOOKUP(D2,'MIles flown'!$A$2:$B$44,2,FALSE)
Step 2: Copy the the Cell E2 and paste it till last available row value of frequent flyers no.
Step 3: Select from E2 to E44 (Last avalable value of Miles Flown) --> Go to "Conditional Formatting" option under home ribbon of MS Excel . Select "Highlighting Cell Rules" --> Select "Less Than"--> Put the reference value "4000" in the dialogue box. Select the color to highlight. Select "OK". All cells with less than 4000 miles travel will be highlighted.
Step 4: Select A1 to E44 (All columns and rows with a value). Go to "Sort / Filter " option under Home Ribbon in MS Excel. Select custom sort. Check the box "My data has a Header". Select "Sort By Column " as 'Miles Flown'. Select Sort by Values. Select "Order" as 'Largest to Smallest'. Select OK. All rows will be sorted in descending order.
Last_Name (Column A)First_Name
(Column B)
Phone_Number (Column C)Frequent_Flier_Number
(Column D)
Miles Flown
(Column E) Blank