'Filtering Excel Data Instances
Having trouble filtering data within a table (see below) where I want to distinguish the Unique ID column and study analytics on the first instances of IDs vs the second instances of the same IDs using COUNTIFS to sort using the remaining columns. If the unique ID shows up for the first time, it's labeled an input, and if it's the second time, it's an output. But I don't want to create a separate column because that would interfere with other formulae, so I can't figure out how to sort the first column.
For example, I would want all instances of inputs that were completed by John during Q1 of 2020 with completion = 100% and Deadline Met = Yes.
I have tried using
=UNIQUE(FILTER([TableData], (E:E = "Yes") * (D:D = 100%) * (C:C > 12/31/2019) * (B:B = "John"))
and also
=COUNTIFS(B:B, "John", C:C, '>'&DATE(12/31/2019), D:D, >75%, E:E, "Yes")
| Unique ID | Names | Date | Completion % | Deadline Met |
|---|---|---|---|---|
| A1 | John | 1/1/2020 | 100% | Yes |
| A1 | John | 1/3/2020 | 100% | No |
| B2 | Jack | 1/2/2020 | 100% | Yes |
| B2 | Jack | 1/6/2020 | 20% | No |
| C3 | John | 1/7/2020 | 50% | No |
| C3 | John | 1/9/2020 | 0% | Yes |
I'm not sure if I could filter with a dynamic array (=filter(index(x,y,z)))?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|