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
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer (Col A) | |||||
| 2 | Zara | Target [Enter Formula] | ||||
| 3 | Apex Corp | |||||
| 4 | Zara | |||||
| 5 | Beta LLC | |||||
| 6 | Apex Corp | |||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect