List Allergies in Grade 1
I need a unique list of all food allergies reported for students in the first grade.
=UNIQUE(FILTER(C2:C10, B2:B10=1)) How it works: The `FILTER` function first narrows down the list of allergies (from C2:C10) to only those associated with students whose grade (in B2:B10) is equal to '1'. This creates a dynamic array of all allergies reported by first-grade students, including duplicates. The `UNIQUE` function then takes this filtered array and extracts only the distinct values, providing a clean, non-redundant list of all food allergies specific to Grade 1 students.
Data Setup
| Student Name | Grade | Allergy |
|---|---|---|
| Alice | 1 | Peanuts |
| Bob | 2 | Dairy |
| Charlie | 1 | Gluten |
| David | 1 | Peanuts |
| Eve | 3 | Shellfish |
| Frank | 1 | Dairy |
| Grace | 2 | Gluten |
| Heidi | 1 | Peanuts |
| Ivan | 1 | Soy |
Step-by-Step Guide
Ensure your student data is organized with columns for 'Student Name', 'Grade', and 'Allergy'.
Identify the column containing the student grades (e.g., Column B) and the column containing the reported allergies (e.g., Column C).
In an empty cell where you want the unique list to appear, start by using the FILTER function to select only the allergies for students in Grade 1. The syntax will be `FILTER(Allergy_Range, Grade_Range=1)`.
Wrap the entire FILTER function with the UNIQUE function to remove any duplicate allergies from the filtered list. The final formula will be `=UNIQUE(FILTER(C2:C10, B2:B10=1))`.
Press Enter to display the unique list of allergies for first-grade students.