Excel
All Top 50 Excel & Business Modeling Questions#42
Generating Sorted Unique Dropdown Sources with UNIQUE and SORT
EasyDeloitteInterview Question #42
Asked at DeloitteIn Column A (`A2:A100`), customer names appear repeatedly with duplicates. Write a single formula in `C2` that extracts all unique customer names, sorts them alphabetically from A to Z, and spills the result down.
Input Table: CustomerTransactions (A1:A6)
5 rows preview| Customer (Col A) |
|---|
| Zara |
| Apex Corp |
| Zara |
| Beta LLC |
| Apex Corp |
Expected Output Structure3 rows
| Sorted Unique Customers (Col C) |
|---|
| Apex Corp |
| Beta LLC |
| Zara |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
C2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Customer (Col A) | |||||||||
| 2 | Zara | Target [Enter Formula] | ||||||||
| 3 | Apex Corp | |||||||||
| 4 | Zara | |||||||||
| 5 | Beta LLC | |||||||||
| 6 | Apex Corp | |||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell C2 and enter your formula above.Shortcut: Click "Load Solution" to inspect