_Cleansing_Inventory_Data_13_Instructions.docx

_Cleansing_Inventory_Data_13_Instructions.docx

Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH09_GRADER_PS2_AS – Cleansing Inventory Data 1.3

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: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded workbook named e05ch09_grader_a1_Inventory.xlsx. Save the file with the name e05ch09_grader_a1_Inventory_LastFirst.xlsx, using your last and first name. If necessary, enable the content. 0.000 2 Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber. 15.000 3 Enter the text Step 1 in cell G1. 5.000 4 The data in column C has nonprintable characters before and after the data contained in each cell. In cell G2, enter a formula to remove any nonprintable characters from column C. Copy the formula down the column. 12.000 5 Enter the text Step 2 in cell H1. 5.000 6 There are several spaces before and after the data in column G that need to be removed. In cell H2, enter a formula to remove any extra spaces in the data from column G. Copy the formula down the column. 12.000 7 The category and manufacturer should be in two separate columns. Enter the text Category in cell I1. 5.000 8 Enter the text Manufacturer in cell J1. 5.000 9 Use Flash Fill to place the category data from column H in proper case in column I. Use Flash Fill to place the Manufacturer data from column H into the new column J. Owing to the nature of the data, Flash Fill will need to be invoked from the Data tab. Mac users will need to use the Text to Columns feature. Select the range H2:H24. On the Data tab, click Text to Columns. Select Space as the Delimiter and =$I$2 as the Destination. 21.000 10 Enter the text InvCode in cell K1. In cell K2, enter a formula to display the inventory code from column A as all uppercase letters. Copy the formula down the column. 10.000 11 Enter the text ItemCode in cell L1. The ItemCode is a combination of the inventory code in all uppercase letters, with the item number appended to it. For example, the first ItemCode should be RLXF920569. Use the appropriate function in column L to create this new ItemCode and AutoFill the function down to L20. 10.000 12 Save the workbook. Close the workbook. Exit Excel. Submit the workbook as directed. 0.000 Total Points 100.000

Updated: 05/01/2017 1 Current_Instruction.docx

li_e05ch09_grader_a1_Inventory.xlsx

Inventory

InventoryCode ItemNumber Category/Manufacturer SellingPrice WholeSale Units/Description
rLXf 920569 � BRAKES NAPA � 44.99 41.94 4 pads
lJDf 400632 � TOOLS NAPA� 16.25 12.34 4 grinding wheels
lJDf 400632 � TOOLS NAPA� 16.25 12.34 4 grinding wheels
bCDx 914314 � Paint 3M � 18.4 14.48 3 spray gun filters
kRBa 933521 � EQUIPMENT Balkamp � 9.99 7.89 3 antifreeze testers
lQSa 402860 � CHEMICALS NAPA� 48.49 35.18 5 gallons RV antifreeze
bLMv 009225 � Paint Sharpe � 6 4.62 1 spray gun service kit
fOXt 030594 � SHOCKS KYB� 89.99 75.00 2 rear shocks
nXWq 041141 � CHEMICALS Altrom Chemicals � 37.99 25.51 6 cans fuel injector cleaner
tNDk 741102 � OIL Pennzoil� 5.25 3.94 1 qt
eSZw 009216 � WELDERS Vise Grip � 22.19 13.53 3 rolls metal solder
qKOm 895850 � Paint Evercraft � 9.99 6.19 5 spray gun nozzle gaskets
mTHa 091160 � EQUIPMENT Viper � 12.49 10.62 3 antifreeze filters
lQSa 402860 � CHEMICALS K&W� 18.49 13.18 5 cans radiator stop leak
eWHp 500129 � OIL Quaker State � 3.99 3.31 1 qt
eCEz 009256 � ACCESSORIES NAPA � 8.5 6.35 1 universal license plate light
sYKp 424076 � TOOLS DeWalt � 249.49 203.65 1 grinder
nQUq 874000 � SHOCKS NAPA � 13.09 11.55 2 shock mate boot kits
gLYh 930777 � BRAKES McGard � 10.79 6.14 4 locks
eCEz 009256 �ACCESSORIES Balkamp � 3.5 2.35 2 yellow round side markers
bLMv 009225 � Paint 3M � 26 24.62 6 rolls masking tape
aWHw 873700 � WELDERS Balkamp � 26.49 20.09 4 soldering gun tips
uIRb 883466 � SEALANTS Permatex � 16.49 12.00 6 tubes gasket sealant
rLXf 920569 � BRAKES Balkamp � 5.45 2.94 1 spindle lock nut kit

&F


Comments are closed.