SUBMISSION: Module 2

📥 Starter File

⬇ Download Starter File (.xlsx)

The file contains two sheets: Raw Sales (messy data) and Product Catalog (reference table). An Instructions sheet is also included.

---

📋 Assignment Instructions

Part 1: Data Cleaning (Raw Sales sheet)

1. Fix inconsistent customer names — standardize all names to Title Case (e.g., "john smith" → "John Smith"). Use PROPER() or Flash Fill.
2. Split the City-State column — use Text to Columns (delimiter: hyphen) to separate City and State into two columns.
3. Standardize dates — ensure all dates in column B are in a consistent format (DD-MMM-YYYY). Fix any text-formatted dates.
4. Handle missing values — find rows with blank Customer Name or City (rows 8, 23, 37). Fill them with "Unknown" or remove the rows. Document your choice.
5. Remove duplicates — identify duplicate customer entries (same email, different name casing) and clean them up.

Part 2: Lookup Functions (add new columns to Raw Sales)

6. Add "Product Name" — use VLOOKUP or XLOOKUP to pull the product name from the Product Catalog sheet using the Product Code.
7. Add "Category" — use another lookup to pull the product category.
8. Add "Total Amount" — calculate Quantity × Unit Price for each row.
9. Add "Price Check" — use IF() to compare the Unit Price with the Standard Price from the catalog:
   • Within 10% → "OK"
   • More than 10% above → "OVERPRICED"
   • More than 10% below → "DISCOUNTED"

Part 3: Summary

10. Create a summary section — on a new sheet or below the data, show total revenue grouped by Category using SUMIF or a small Pivot Table.

---

✅ Submission Checklist

• All customer names standardized to Title Case
• City and State in separate columns
• Dates in consistent format
• Missing values handled
• Product Name and Category columns added via lookup
• Total Amount and Price Check columns added
• Summary by category included

Upload your completed .xlsx file using the form below.