27 Feb 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
YO19_Excel_Ch09_Assessment_Cleansing_Inventory_Data
YO19_Excel_Ch09_Assessment_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 in Excel to clean the inventory data.
Start Excel. Open the downloaded workbook named Excel_Ch09_PS2_Inventory.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
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 I1.
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.
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.
Save and close Excel_Ch09_PS2_Inventory.xlsx. Exit Excel. Submit your files as directed.
Grader – Instructions Excel 2019 Project
YO19_Excel_Ch09_Assessment_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 in Excel to clean the inventory data.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Open the downloaded workbook named Excel_Ch09_PS2_Inventory.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. |
0 |
|
2 |
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. |
15 |
|
3 |
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. |
5 |
|
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 |
|
5 |
Enter the text Step 2 in cell H1. |
5 |
|
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 |
|
7 |
The category and manufacturer should be in two separate columns. Enter the text Category in cell I1. |
5 |
|
8 |
Enter the text Manufacturer in cell J1. |
5 |
|
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. |
21 |
|
10 |
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. |
10 |
|
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 UARM423949. Use the appropriate function in column L to create this new ItemCode and fill the function down the column. |
10 |
|
12 |
Save and close Excel_Ch09_PS2_Inventory.xlsx. Exit Excel. Submit your files as directed. |
0 |
|
Total Points |
100 |
Created On: 02/23/2021 1 YO19_Excel_Ch09_Assessment – Cleansing Inventory Data 1.1
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Wridemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.