List Suppliers Used in Q1
As a Procurement Officer, I have a massive ledger of purchases spanning multiple quarters. I need to quickly generate a unique list of all supplier names that we engaged with specifically in the first quarter (January, February, March) to analyze spending patterns and supplier relationships for Q1.
=UNIQUE(FILTER(B2:B10, (MONTH(A2:A10)>=1)*(MONTH(A2:A10)<=3))) How it works: This formula combines the power of `FILTER` and `UNIQUE`. First, `FILTER` is used to narrow down the entire list of supplier names (B2:B10) to only those entries where the corresponding date (A2:A10) falls within the first quarter (months 1, 2, or 3). The `(MONTH(A2:A10)>=1)*(MONTH(A2:A10)<=3)` part creates an array of TRUE/FALSE values, which `FILTER` uses to include or exclude rows. Once `FILTER` returns the list of all Q1 suppliers (including duplicates), the `UNIQUE` function then processes this filtered list, extracting and displaying only the distinct supplier names, providing the Procurement Officer with the exact information needed.
Data Setup
| Date | Supplier Name | Amount |
|---|---|---|
| 2023-01-05 | Office Supplies Inc. | 150 |
| 2023-01-12 | Tech Solutions Ltd. | 500 |
| 2023-01-20 | Office Supplies Inc. | 200 |
| 2023-02-01 | Logistics Co. | 300 |
| 2023-02-15 | Tech Solutions Ltd. | 750 |
| 2023-03-10 | Office Supplies Inc. | 100 |
| 2023-03-25 | Logistics Co. | 400 |
| 2023-04-01 | New Vendor Corp. | 600 |
| 2023-04-10 | Office Supplies Inc. | 250 |
Step-by-Step Guide
Ensure your purchase ledger has a 'Date' column (e.g., Column A) and a 'Supplier Name' column (e.g., Column B).
Identify the range containing your supplier names (e.g., B2:B10) and the range containing your dates (e.g., A2:A10).
To filter for the first quarter, you'll use the `MONTH()` function. The condition `(MONTH(A2:A10)>=1)*(MONTH(A2:A10)<=3)` checks if the month of each date falls between January (1) and March (3).
Nest this condition within the `FILTER` function: `FILTER(B2:B10, (MONTH(A2:A10)>=1)*(MONTH(A2:A10)<=3))`. This will return a list of all supplier names that appear in Q1, including duplicates.
Finally, wrap the entire `FILTER` function with `UNIQUE` to get only the distinct supplier names: `=UNIQUE(FILTER(B2:B10, (MONTH(A2:A10)>=1)*(MONTH(A2:A10)<=3)))`.
Enter this formula into any empty cell where you want the unique list to appear.