Easy for someone who knows excel. I attached the instruction below.
Part 1: For the work pages labeled "Chicago, IL" and “San Francisco, CA” perform the following steps: make sure you are consistent on both sheets.
1. Add 2 columns to the right of the Full Name column. Label the first new column "First" and the second new column "Last." Using either spreadsheet functions (e.g., Left, Right, Len, or Find) or text-to-columns (this is easier, but does not work on earlier versions of Excel), split the Full Name column into a First Name column and a Last Name column.
2. Next change the contents of the Full Name column to be in "Last Name, First Name" order. You may have to create another column to do this and then hide the column.
3. Add another column after the “Job Title, and label it "Location." Place the worksheet name in each row of this column for each employee. Use either Copy and Paste or Fill Down operations.
4. Change the Manager column contents to be in "Last Name, First Name" format. You may add temporary columns to do this and then delete them afterward. (If you use formulas that reference temporary columns, you cannot delete the temporary columns. Hint: Copy the calculated formula results, and paste values on top of them.)
5. Adjust column widths for the best fit of all data.
Part 2: For the worksheet labeled "Tokyo, Japan," perform the following steps:
1. Add a column to the left of the First column. Label it "Full Name."
2. Using spreadsheet functions or operations populate the Full Name column using the format "Last Name, First Name."
3. Add another column after the “Job Title”, and label it "Location." Place the worksheet name in each row of this column for each employee.
4. Change the job titles to match Chicago/San Francisco job titles using the following information: You will need nest IFs inside an IF’s.
Tokyo Title Chicago/San Francisco Title
Assistant Administrative Assistant
Customer Service Level 1 Customer Service Representative
Customer Service Level 2 Software Analyst
5. Convert the Salary column contents from Japanese yen to U.S. dollars using the conversion rate 1 U.S. dollar = [url removed, login to view] Japanese Yen. You may add temporary columns to do this and delete them afterward.
6. Add a column, and label it "Manager." Populate this new column. All employees report to the manager in Tokyo. The manager in Tokyo reports to the CEO in Chicago. Use the format "Last Name, First Name" for this column.
Part 3: Create a new worksheet, and call it "Combined."
1. Copy the data rows from the other three sheets into the new worksheet. Make sure the merged data columns align (i.e., contain the correct data). You may need to Paste Special or Paste Values to get the correct data into the correct columns.
2. Sort the worksheet labeled "Combined" by the following levels:
o Location Ascending
o Manager Ascending
o Job Description
o Full Name
3. Turn on Filtering. Use Filtering tools to eliminate the job titles. (You may want to wait on steps 3 and 4 until after Part 4)
o Office Manager
o Assistant Office Manager
o Administrative Assistant
o Any blanks that may be in your data
4. Using the Subtotal tool, and at each change in job title, do the following:
o Use the Average function.
o Add Subtotal to Salary (and nothing else).