List Active Sellers
I need a unique list of Seller IDs that have generated at least one sale in the last 24 hours.
=UNIQUE(FILTER(A2:A9, B2:B9 > (NOW()-1))) How it works: This formula combines `UNIQUE` with `FILTER` to achieve the desired result. The `FILTER` function first narrows down your sales data to only include rows where the 'Sale Timestamp' (B2:B9) is greater than the current date and time minus one day (`NOW()-1`), effectively capturing all sales within the last 24 hours. From this filtered list, it extracts only the 'Seller ID' column (A2:A9). Finally, the `UNIQUE` function takes this list of recent Seller IDs and returns only the distinct (non-duplicate) values, providing a clean list of active sellers.
Data Setup
| Seller ID | Sale Timestamp |
|---|---|
| S001 | 2023-10-26 10:00:00 |
| S002 | 2023-10-26 13:00:00 |
| S001 | 2023-10-26 14:00:00 |
| S003 | 2023-10-25 09:00:00 |
| S004 | 2023-10-27 08:00:00 |
| S002 | 2023-10-26 15:00:00 |
| S005 | 2023-10-20 10:00:00 |
| S001 | 2023-10-27 09:00:00 |
Step-by-Step Guide
Ensure your sales data is organized with 'Seller ID' in one column (e.g., Column A) and 'Sale Timestamp' in another (e.g., Column B).
Make sure the 'Sale Timestamp' column is formatted as a date and time.
Select an empty cell where you want the unique list of active sellers to appear.
Enter the formula: `=UNIQUE(FILTER(A2:A9, B2:B9 > (NOW()-1)))` (adjust ranges A2:A9 and B2:B9 to match your actual data).
Press Enter. Excel will spill the unique list of Seller IDs who have made a sale in the last 24 hours.