Assignment: Cleansing Inventory Data
Project Description:
You currently work part-time in an automotive parts store. Because word of your knowledge of Excel has spread, you have been contacted by the district manager. The database used for keeping track of inventory has been corrupted, causing some issues with the inventory data. You have been asked to use your expertise of Excel to clean the inventory data.
Instructions
1 Start Excel. Open the downloaded workbook named e05ws09_grader_h1.xlsx. Save the file with the name e05ws09_grader_h1_LastFirst.xlsx, replacing LastFirst with your name.
2 Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber.
3 Enter the text Step 1 in cell G1.
4 The word PAINT was misspelled as PIANT throughout the Category/Manufacturer column. In cell G2, use a formula to substitute all spellings of PIANT with PAINT from column C. Copy the formula down the column.
5 Enter the text Step 2 in cell H1.
6 The data in column G has nonprintable characters before and after the data contained in each cell. In cell H2, enter a formula to remove any nonprintable characters from column G. Copy the formula down the column.
7 Enter the text Step 3 in cell I1.
8 There are several spaces before and after the data in column H that need to be removed. In cell I2, enter a formula to remove any extra spaces in the data from column H. Copy the formula down the column.
9 The category and manufacturer should be in two separate columns. Enter the text Category in cell J1.
10 Enter the text Brakes, Tools, and Paint in cells J2, J3, and J4, respectively. Use Flash Fill to place the category data from column I in proper case in column J.
11 Enter the text Manufacturer in cell K1.
12 Enter the text NAPA, NAPA, and 3M into cells K2, K3, and K4, respectively. Use Flash Fill to place the Manufacturer data from column I into the new column K. Owing to the nature of the data, Flash Fill will need to be invoked from the DATA tab.
13 Enter the text InvCode in cell L1. In cell L2, enter a formula to display the inventory code from column A as all uppercase letters. Copy the formula down the column.
14 Enter the text ItemCode in cell M1. The ItemCode is a combination of the inventory code in all uppercase letters (column L), with the item number (column B) appended to it. For example, the first ItemCode should be RLXF92569. In cell M2, enter a formula to create this new ItemCode for all items in the data. Copy the formula down the column.
15 Save the workbook. Close the workbook. Exit Excel. Submit the workbook as directed.
Attachment:- lastname_e05ws09_grader_h1.xlsx