Practice and Answer Key: Cleaning a Messy Export
Goal of this practice: Clean IDs, city names and text numbers, then remove duplicates and get the right total.
या practice चं ध्येय: IDs, शहरांची नावं आणि text numbers साफ करा, मग duplicates काढा आणि बरोबर total मिळवा.
इस practice का लक्ष्य: IDs, शहरों के नाम और text numbers साफ़ करें, फिर duplicates हटाएँ और सही total पाएँ.
Suppose Zoya downloaded the Blinkit export twice and pasted both into one sheet. Some IDs are lower case, city names have spaces, and the amounts are stored as text. All data is fictional.
| A: Order ID | B: City | C: Amount (text) |
|---|---|---|
| " blk-2201" | "pune " | "64" |
| "BLK-2201" | "Pune" | "64" |
| "BLK-2202" | "NASHIK" | "270" |
| "AMN-2203" | " nagpur" | "240" |
| "BLK-2204" | "Kolhapur " | "232" |
(The quotes only show where the spaces are.)
Exercise (one under the other):
- In D2, clean the ID:
=UPPER(TRIM(A2)). - In E2, clean the city:
=PROPER(TRIM(B2)). - In F2, turn the amount into a number:
=VALUE(C2). - Copy D2:F2 down to row 6.
- Copy D:F and Paste Special › Values onto a new sheet.
- Select the clean data and click Data › Remove Duplicates, tick only Order ID.
- Total the Amount column.
Expected result (check your numbers):
| Order ID | City | Amount (₹) |
|---|---|---|
| BLK-2201 | Pune | 64 |
| BLK-2202 | Nashik | 270 |
| AMN-2203 | Nagpur | 240 |
| BLK-2204 | Kolhapur | 232 |
4 rows. Total = 64 + 270 + 240 + 232 = ₹806. Before cleaning, =SUM(C2:C6) shows 0 because the amounts are text, and after only VALUE (without removing duplicates) the total is ₹870.
Answer key (open after you try)
D2: =UPPER(TRIM(A2))
E2: =PROPER(TRIM(B2))
F2: =VALUE(C2)
Data › Remove Duplicates › tick Order ID only
Total: =SUM(F2:F5)
Clean the key before removing duplicates. " blk-2201" and "BLK-2201" are different text until TRIM and UPPER make them equal.
Common mistakes:
| What you see | Why | Fix |
|---|---|---|
| 5 rows remain after Remove Duplicates | Duplicates were removed before cleaning the ID | Clean with TRIM and UPPER first, then remove duplicates |
| SUM shows 0 | Amounts are still text | Use VALUE or Convert to Number |
| "Kolhapur" does not match in a lookup | Trailing spaces are still there | Wrap the city in TRIM |
| Formulas break after deleting the raw columns | The clean columns still point to them | Paste Special › Values before deleting |
Ravindra Bagale's rule: clean the key, then remove duplicates, then total. Always in that order.
Thodkyaat sangaycha tar (quick recap)
Raw data kadhich badlu naka; aadhi standardise (TRIM, CLEAN, CHAR(160), case, city mapping), mag duplicates aani blanks, mag types (VALUE, Text to Columns, DATE), mag codes/phones/e-mails, shevti outliers aani errors flag kara – aani rows va totals reconcile kara. Roj yenarya data sathi Power Query. Aata pudhe jaauya – Tables, sorting aani filtering.
Loading practice tasks...
- Clean a name (Easy): Clean B2.
- Missing phones (Easy): How many phones are empty in C2:C7?
- Medicine in proper case (Easy): Clean D4.
- Repeat patient (Easy): How many bills are for "rekha sharma" after TRIM?
- Clean phone (Medium): Remove spaces and dashes from C3.
- Amount as a number (Medium): Change E4 into a number.
- Real date (Medium): Make a real date from F5.
- Is the date missing? (Medium): Is F7 empty? (TRUE or FALSE)
- Total sales (Hard): Total of E2:E7 as numbers.
- Different patients (Hard): How many different cleaned names are in B2:B7?
- Bill line (Hard): Make "Sanjay Gupta - Ibuprofen 400 - 60" from row 6.