Grader Instructions For Excel 2019 Project Day 19 Assessment

Grader Instructionsexcel 2019 Projectyo19 Excel Ch09 Assessment Cle

Open the downloaded workbook named Excel_Ch09_PS2_Inventory.xlsx. Save the file to your preferred storage location. Remove duplicate records in the Inventory worksheet where duplicates are defined by matching InventoryCode and ItemNumber. In cell G1, enter "Step 1" to label the first step. In cell G2, input a formula to remove nonprintable characters from column C, then copy this formula down for all data rows. In cell H1, enter "Step 2". In cell H2, input a formula to eliminate extra spaces from column G, then copy it down. Label columns I and J as "Category" and "Manufacturer", respectively. Use Flash Fill to extract the Category and Manufacturer data into columns I and J, formatting the text in proper case. Create a new column K labeled "InvCode" and generate inventory codes in uppercase letters from column A by applying the UPPER function and copying it down. In column L, labeled "ItemCode", concatenate uppercase InventoryCode from column K with ItemNumber from column B to create the full ItemCode (e.g., UARM423949). Save the updated workbook and close Excel. Submit the file as instructed.

Paper For Above instruction

In this project, the primary objective is to cleanse and prepare an automotive parts inventory dataset using Excel 2019. Efficient data cleaning techniques such as identifying and removing duplicate records, handling non-printable characters, trimming excess spaces, and transforming text case are fundamental skills in data management. These steps ensure that the inventory data is accurate, consistent, and ready for analysis or reporting. This process reflects real-world scenarios where data quality can significantly impact decision-making and operational efficiency.

Initially, the task involves opening the provided Excel workbook named "Excel_Ch09_PS2_Inventory.xlsx" and saving it locally to preserve changes. Removing duplicates is the first step, which ensures each InventoryCode and ItemNumber combination is unique. Excel offers a straightforward feature under the Data tab that facilitates the removal of duplicate rows based on selected columns, effectively cleaning the dataset from redundant entries. This step enhances the integrity of subsequent data analysis by eliminating discrepancies caused by duplicated records.

The next phase involves handling textual inconsistencies within the Category and Manufacture fields. Non-printable characters may be introduced during data collection or transfer, leading to data anomalies. The formula =CLEAN(C2) in cell G2 effectively removes these characters from the data in column C, and copying this formula down ensures all entries are cleansed. Following this, extra spaces—both leading and trailing—are addressed using the TRIM function =TRIM(G2), which standardizes the text, making subsequent processing more reliable.

Once the data is cleaned, it must be split into separate categories: Category and Manufacturer. Using Flash Fill, a powerful tool introduced in Excel 2013, expedites this separation process by recognizing patterns in the data. After manually entering the desired output for the first entry in columns I and J, activating Flash Fill (via the Data tab or using Ctrl+E) populates the rest of the column based on the pattern, significantly reducing manual effort and potential errors. This approach ensures the data is structurally organized into meaningful fields.

The creation of the InvCode is another essential step. This code combines the InventoryCode—initially in mixed case—and transforms it into uppercase with the UPPER function. Applying =UPPER(A2) in cell K2 standardizes the inventory codes, which is vital for consistency, especially if codes are used as identifiers or keys in databases. Copying the formula down updates all records.

Finally, a new ItemCode is constructed by concatenating the uppercase InventoryCode from column K with the ItemNumber from column B. This concatenation creates a unique identifier for each inventory item that combines essential details into a single string. Using the formula =K2 & B2 in column L achieves this, enabling streamlined referencing and tracking of items within the inventory management system.

The entire data cleansing process underscores the importance of meticulous data preparation in the retail and supply chain environments. Clean, standardized, and unique identifiers facilitate efficient inventory management, reduce errors in order fulfillment, and improve reporting accuracy. Mastery of Excel's functions and features—such as formulas, Flash Fill, and data tools—is crucial for professionals managing large datasets. This project provides practical experience in deploying these techniques to solve real-world data quality issues.

References

  • Excel Data Cleaning and Deduplication. (2021). Microsoft Support. https://support.microsoft.com
  • Introduction to Excel's CLEAN and TRIM Functions. (2020). Excel Jet. https://exceljet.net
  • Using Flash Fill in Excel. (2022). Microsoft Tech Community. https://techcommunity.microsoft.com
  • Data Cleansing Techniques for Business Intelligence. (2019). Journal of Data Management. https://jdmjournal.com
  • Excel Formulas & Functions for Data Cleaning. (2021). LinkedIn Learning. https://linkedin.com/learning
  • Understanding Data Deduplication. (2020). DataQualityPro. https://dataqualitypro.com
  • Best Practices in Inventory Data Management. (2018). Supply Chain Quarterly. https://supplychainquarterly.com
  • Automating Data Cleansing with Excel. (2022). Datawrapper Blog. https://blog.datawrapper.de
  • Data Standardization in ERP Systems. (2019). ERP Today. https://erptoday.com
  • Effective Use of CONCATENATE in Excel. (2020). Excel Campus. https://excelcampus.com

Related Assignments