UNIQUE with SORT for a department list
IntermediateDynamic array functions can be nested inside each other to produce more useful results in a single formula. When you need a deduplicated list that is also sorted, wrapping UNIQUE inside SORT returns a clean alphabetized list that updates automatically whenever the source data changes.
UNIQUE returns each distinct value from a range once. SORT reorders values in ascending or descending order. By nesting them, you get the best of both.
The range you pass to UNIQUE should be the column you want to scan for repeated values. SORT then takes the deduplicated result and arranges it in the order you specify.
In this sheet, the employee roster has several people in the same department. The task is to create a reporting-ready list that shows each department name once, in alphabetical order, using a single formula in the output area.
Your task:
Enter one formula in A17 that returns every department name exactly once, sorted from A to Z. Let the result spill into the cells below automatically.
Need some help?
Hint 1
Start by identifying which column in the employee table contains department names. Pass only that single column range to UNIQUE so it compares individual values rather than entire rows.
Hint 2
Once UNIQUE returns the seven distinct department names, wrap the whole expression in SORT. With no second argument, SORT defaults to ascending order, which gives you the A to Z list you need.
UNIQUE with SORT for a department list
IntermediateDynamic array functions can be nested inside each other to produce more useful results in a single formula. When you need a deduplicated list that is also sorted, wrapping UNIQUE inside SORT returns a clean alphabetized list that updates automatically whenever the source data changes.
UNIQUE returns each distinct value from a range once. SORT reorders values in ascending or descending order. By nesting them, you get the best of both.
The range you pass to UNIQUE should be the column you want to scan for repeated values. SORT then takes the deduplicated result and arranges it in the order you specify.
In this sheet, the employee roster has several people in the same department. The task is to create a reporting-ready list that shows each department name once, in alphabetical order, using a single formula in the output area.
Your task:
Enter one formula in A17 that returns every department name exactly once, sorted from A to Z. Let the result spill into the cells below automatically.
Need some help?
Hint 1
Start by identifying which column in the employee table contains department names. Pass only that single column range to UNIQUE so it compares individual values rather than entire rows.
Hint 2
Once UNIQUE returns the seven distinct department names, wrap the whole expression in SORT. With no second argument, SORT defaults to ascending order, which gives you the A to Z list you need.