Please help! :)

Assume you have graduated from SHU and gained employment at the lucrative firm of Funck inc. Your (unreasonable) boss provides you the following data with the directive: I have a meeting in 10 minutes, and I need the following employee list parsed into separate fields (pottem shown below! want to see the department name, not just the code (refer to the lookup table) I want the total salary for each department (C49:CS4), and I want a count of the employees who earn less a specified amount, initially, set the cut-off salary level at $100,000 (CS9), but create your worksheet so that I can dynamically change the salary level in cell C59 and/or the department names in column 8 (849-854) Step 1: Use the pattern shown below to parse the data into separate fields 10 Pattern: Last Name First Name Employee ID1Department code Salary **You may reamange the column headings AND/OR ADD new columns if needed Ther are no spaces in the text strings 11 12 Raw Data Parsed Data: 33 Last Name First Name Employee ID Department Code Salary-Parsed 14an Becky 8321A2/110567 15 Smith Sallie 4301P20194569 16 Sherril, Karen,5587 MAI102309 17 Jesswein Kurt 46148922 18 Houston Samuel,7731A111957304 19 Molina Yadier 9221P11254679 20 Stretcher Robert,9231020195902 21 Howard Jamison,23918829 23 Step 2: Convert the parsed Salary field (from above) into a numeric field using the NUMBERVALUE Function. Place the numeric salary values in the range C26C33. Record Salary-Numeric Becky Jilian Sallie Smith Karen Sherril Kurt Jesswein Samuel Houston Maut.... ANARARE 27 DO NOT USE THE TEXT TO COLUMNS OPTION TO USE THE TEXT FUNCTIONS DEMONSTRATED IN LECT DO NOT HARD CODE ANY FUNCTION ARGUMENTS 21 Howard Jamison,2391H118829 22 23 Step 2: Convert the parsed Salary field (from above) into a numeric field using the NUMBERVALUE Function. Place the numeric salary values in the range C26:33, 24 Record Salary-Numeric Becky Jillian Sallie Smith Karen Sherril Kurt Jesswein Samuel Houston 31 Yadier Molina Robert Stretcher 33 Jamison Howard Step 3: Use a lookup function (VLOOKUP, XLOOKUP, or INDEX/MATCH) to display the name of the department in the range C37 C44 (the parsed Department Code above will serve as the lookup value) Department Name Record Department Lookup Table Code Becky Jillian Sallie Smith Karen Sherril MA Name Maintenance Accounting A1 P20 Kurt Jesswein Samuel Houston Yadier Molina Purchasing Payroll PL 42 H Human Resources Robert Stretcher A2 Advertising Jamison Howard 46 Step 4: Use a conditional statement to sum the Salary by departement. The Department Name in column B serves as the criteria so that that I can dynamically change the department names in column 8 and the w 47 48 Total Salary by Department 49 Purchasing 50 Payroll Charaons Hot and Maintance Latatamant String 2 + K E CURL Les Sallie Smith MA Maintenance 39 Karen Sherri A1 Accounting Purchasing Kurt Jesswein P2U 41 Samuel Houston PL Payroll 42 Yadier Molina H Human Resources Robert Stretcher A2 Advertising 44 Jamison Howard 46 Step 4: Use a conditional statement to sum the Salary by departement. The Department Name in column serves as the criteria so that that I can dynamically change the department names in column B and the worksheet will updat 48 Total Salary by Department Purchasing Payroll 51 Maintenance **Ensure you use bose your conditional statement on the numerical NOT porsed salary data 52 Human Resources 33 Advertising Accounting 54 $5 56 Step 5: Use a conditional statement to count the number of employees who earn less than $100,000. 57 initially, set the salary level at $100,000 (C59), but create your worksheet so that I can dynamically change the salary level in cell C59 58 59 Salary Level $100,000 60 Employees salary