4 5 6 8 9 2 3 10 11 There are duplicate rows of data in...

Transcribed Image Text:

4 5 6 8 9 2 3 10 11 There are duplicate rows of data in the Inventory worksheet. These duplicate records need to be removed before the data can be further cleansed. Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber The data for the Category/Manufacture has extra spaces, non-printable characters, and the case of the text needs altered in order to successfully cleanse the data. This process will take several steps and it will be easier if each step is completed in separate columns. Enter the text Step 1 in cell G1 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. Enter the text Step 2 in cell H1. 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 The category and manufacturer should be in two separate columns. Enter the text Category in cell 11. Enter the text Manufacturer in cell J1. 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. A new ItemCode will be constructed from the InventoryCode and ItemNumber columns. The first part of the ItemCode will be the InventoryCode displayed as uppercase letters. 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. 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 UARM423949. Use the appropriate function in column L to create this new ItemCode and fill the function down the column. 13 A B InventoryCode ItemNumber Category/Manufacturer DACCESSORIES Balkamp 2 uaRM 423949 3 ULkk 653258 BRAKES Balkamp vrrL 708778 OIL Quaker State OIL Pennzoil Paint Sharpe 5 mExy 5 KUKK 7aQky 3 xgag UkgH 0 VanV 1 kkQk 2 Hbbb 3 KKmp 4 NBEZ 5 ULKK 6 ajjj 7 ipip 8 HmmS 9 IMIS 0 Xkmk 1 ppog 2 Qqal 3 fssf 4 eHge 5 aaay 6 InzF1 7 VqnV 8 Hbbb. 9 10 1 2 3 4 S 16 7 18 9 0 1 2. 946463 703086 131819 756385 811862 127037 971190 040715 081885 623171 653258 299429 186104 183636 714742 128797 274771 577395 532981 282483 117903 836202 127037 040715 Inventory BRAKES McGard Paint Evercraft ACCESSORIES NAPA EQUIPMENT Balkamp EQUIPMENT Viper SHOCKS NAPA SEALANTS Permatex TOOLS NAPA BRAKES Balkamp TOOLS NAPA CHEMICALS K&W WELDERS Vise Grip Paint 3MD WELDERS Balkamp Paint 3M CHEMICALS Altrom Chemicals CHEMICALS NAPAD BRAKES NAPA SHOCKS KYBO TOOLS DeWalt EQUIPMENT Balkamp SHOCKS NAPA D E Selling Price Wholesale Units/Description 6.5 8.45 6.99 8.25 9 13.79 12.99 11.5 12.99 15.49 16.09 19.49 19.25 8.45 19.25 21.49 25.19 21.4 29.49 29 40.99 51.49 47.99 92.99 252.49 12.99 16.09 5.35 2 yellow round side markers 5.9355 1 spindle lock nut kit 6.3117 1 qt 6.94 1 qt 7.62 1 spray gun service kit 9.14 4 locks 9.19 5 spray gun nozzle gaskets 9.35 1 universal license plate light 10.89 3 antifreeze testers 13.62 3 antifreeze filters 14.55 2 shock mate boot kits, 15.00 6 tubes gasket sealant 15.34 4 grinding wheels 5.9355 1 spindle lock nut kit 15.34 4 grinding wheels 16.18 5 cans radiator stop leak 16.53 3 rolls metal solder 17.48 3 spray gun filters 23.09 4 soldering gun tips 27.62 6 rolls masking tape 28.51 6 cans fuel injector cleaner 38.18 5 gallons RV antifreeze 44.94 4 pads 78.00 2 rear shocks 206.65 1 grinder 10.89 3 antifreeze testers. 14.55 2 shock mate boot kits 4 5 6 8 9 2 3 10 11 There are duplicate rows of data in the Inventory worksheet. These duplicate records need to be removed before the data can be further cleansed. Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber The data for the Category/Manufacture has extra spaces, non-printable characters, and the case of the text needs altered in order to successfully cleanse the data. This process will take several steps and it will be easier if each step is completed in separate columns. Enter the text Step 1 in cell G1 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. Enter the text Step 2 in cell H1. 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 The category and manufacturer should be in two separate columns. Enter the text Category in cell 11. Enter the text Manufacturer in cell J1. 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. A new ItemCode will be constructed from the InventoryCode and ItemNumber columns. The first part of the ItemCode will be the InventoryCode displayed as uppercase letters. 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. 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 UARM423949. Use the appropriate function in column L to create this new ItemCode and fill the function down the column. 13 A B InventoryCode ItemNumber Category/Manufacturer DACCESSORIES Balkamp 2 uaRM 423949 3 ULkk 653258 BRAKES Balkamp vrrL 708778 OIL Quaker State OIL Pennzoil Paint Sharpe 5 mExy 5 KUKK 7aQky 3 xgag UkgH 0 VanV 1 kkQk 2 Hbbb 3 KKmp 4 NBEZ 5 ULKK 6 ajjj 7 ipip 8 HmmS 9 IMIS 0 Xkmk 1 ppog 2 Qqal 3 fssf 4 eHge 5 aaay 6 InzF1 7 VqnV 8 Hbbb. 9 10 1 2 3 4 S 16 7 18 9 0 1 2. 946463 703086 131819 756385 811862 127037 971190 040715 081885 623171 653258 299429 186104 183636 714742 128797 274771 577395 532981 282483 117903 836202 127037 040715 Inventory BRAKES McGard Paint Evercraft ACCESSORIES NAPA EQUIPMENT Balkamp EQUIPMENT Viper SHOCKS NAPA SEALANTS Permatex TOOLS NAPA BRAKES Balkamp TOOLS NAPA CHEMICALS K&W WELDERS Vise Grip Paint 3MD WELDERS Balkamp Paint 3M CHEMICALS Altrom Chemicals CHEMICALS NAPAD BRAKES NAPA SHOCKS KYBO TOOLS DeWalt EQUIPMENT Balkamp SHOCKS NAPA D E Selling Price Wholesale Units/Description 6.5 8.45 6.99 8.25 9 13.79 12.99 11.5 12.99 15.49 16.09 19.49 19.25 8.45 19.25 21.49 25.19 21.4 29.49 29 40.99 51.49 47.99 92.99 252.49 12.99 16.09 5.35 2 yellow round side markers 5.9355 1 spindle lock nut kit 6.3117 1 qt 6.94 1 qt 7.62 1 spray gun service kit 9.14 4 locks 9.19 5 spray gun nozzle gaskets 9.35 1 universal license plate light 10.89 3 antifreeze testers 13.62 3 antifreeze filters 14.55 2 shock mate boot kits, 15.00 6 tubes gasket sealant 15.34 4 grinding wheels 5.9355 1 spindle lock nut kit 15.34 4 grinding wheels 16.18 5 cans radiator stop leak 16.53 3 rolls metal solder 17.48 3 spray gun filters 23.09 4 soldering gun tips 27.62 6 rolls masking tape 28.51 6 cans fuel injector cleaner 38.18 5 gallons RV antifreeze 44.94 4 pads 78.00 2 rear shocks 206.65 1 grinder 10.89 3 antifreeze testers. 14.55 2 shock mate boot kits