Practice and Answer Key: FILTER, SORT and UNIQUE
Goal of this practice: Make a live late-orders list and a category list with dynamic array formulas.
या practice चं ध्येय: Dynamic array formulas ने late orders ची live list आणि category list बनवा.
इस practice का लक्ष्य: Dynamic array formulas से late orders की live list और category list बनाएँ.
Suppose Salman wants a live list of slow orders (more than 12 minutes), slowest first, and a clean list of categories for a drop-down. Use tblMini from Module 3. All data is fictional.
Use tblMini (columns Order ID, Date, City, Platform, Category, Qty, Amount, Mins, Status).
Exercise (one under the other):
- In K2, filter tblMini for Mins greater than 12.
- Wrap it in SORT so the slowest order comes first (Mins is column 8).
- In U2, list each category once, A to Z.
- In W2, count the categories.
- Add a new row with Mins 20 to tblMini and watch K2 update.
Expected result (check your numbers):
| Order ID | City | Mins | Status |
|---|---|---|---|
| BLK-1007 | Solapur | 18 | Delivered |
| BLK-1010 | Nashik | 17 | Delivered |
| AMN-1005 | Kolhapur | 16 | Delivered |
| AMN-1003 | Pune | 14 | Delivered |
| BLK-1009 | Pune | 13 | Cancelled |
5 slow orders. Categories (U2 spill): Bakery, Dairy, Fruits, Home, Snacks, Staples. W2 = 6.
Answer key (open after you try)
K2: =SORT(FILTER(tblMini, tblMini[Mins]>12, "No slow orders"), 8, -1)
U2: =SORT(UNIQUE(tblMini[Category]))
W2: =COUNTA(U2#)
U2# means "the whole spill range that starts in U2". It grows when a new category arrives.
Common mistakes:
| What you see | Why | Fix |
|---|---|---|
| #SPILL! | Something is typed in the cells below K2 | Clear the area the result needs |
| Sorted by the wrong column | sort_index counts columns of the result, not sheet letters | Mins is the 8th column of tblMini, so use 8 |
| #CALC! when nothing matches | No if_empty argument | Add "No slow orders" as the 3rd argument of FILTER |
| W2 always shows 1 | COUNTA(U2) counts one cell | Use the spill reference U2# |
Ravindra Bagale's rule: build the formula in small layers (FILTER, then SORT around it) and check each layer.
Thodkyaat sangaycha tar (quick recap)
Dynamic array formulas spill hotat – jaga rikami theva aani # ne refer kara; FILTER (AND = *, OR = +, if_empty dya), SORT/SORTBY, UNIQUE, SEQUENCE; LET ne formula vachayla sopa, LAMBDA ne swatahche functions; VSTACK/HSTACK ne jodne, CHOOSECOLS/TAKE/DROP ne kaapne. Sagle Microsoft 365 / navin Excel madhe – file share kartana version lakshat theva. Aata pudhe jaauya – Power Query!
Loading practice tasks...
- Different drivers (spill) (Easy): UNIQUE of C2:C8.
- Austin rides (spill) (Easy): Ride IDs where City is "Austin".
- Fares high to low (spill) (Easy): Sort E2:E8 big to small.
- Cities A to Z (spill) (Easy): List each city in B2 to B8 only once, in A to Z order.
- 5-star rides (spill) (Medium): Full rows A2:F8 where Rating is 5.
- Long rides sorted (spill) (Medium): Fares of rides over 6 miles, big to small.
- Amy Chen fares total (Medium): Total fare of "Amy Chen".
- Drivers in Seattle (spill) (Medium): Different drivers in Seattle, A to Z.
- Table sorted by fare (spill) (Hard): Sort A2:F8 by column 5, big to small.
- Fare per city (spill) (Hard): For each city (no repeats, A to Z) from B2 to B8, add up E2 to E8.
- Low-rated in Seattle or Denver (spill) (Hard): Ride IDs with Rating below 5 in Seattle OR Denver.