Forum Discussion
Is there a way to look at a list and return only those that do not contain a property
I have a huge amount of data that I'm looking through. Some IDs contain multiple rows and some contain only one row. I want to see any rows that do not show that locator = C.
Example: I would want to exclude AAABPC because it contains C
I would not want to exclude AAACWI because it does not contain a row that has a B
| AAABPC | B |
| AAABPC | B |
| AAABPC | C |
| AAABPC | C |
| AAACWI | B |
| AAACWI | B |
| AAAHHG | B |
| AAAHHG | C |
| AAAHYY | B |
| AAAHYY | B |
| AAAJXP | B |
| AAALNP | B |
| AAAOOI | B |
| AAAPVI | B |
| AAAPVI | B |
| AAAPVI | C |
| AAAPVI | C |
Can anyone help me find a way to do that please?
Let's say the data are in A1:B1000.
Elsewhere, for example in D1, enter the formula
=FILTER(A1:A1000,COUNTIFS($A$1:$A$1000,$A$1:$A$1000,$B$1:$B$1000,"C")=0)
(This works only if you have Microsoft 365 or Office 2021)
Thanks for your help. I didn't explain what I want very well. In this example:
Record locator AAABPC has multiple rows. Because that locator has some rows with B and some rows with C, I want to ignore that locator.
Since AAACWI only has B in column B then that one can remain. I'm actually using a helper concatenate column to remove all the duplicates before I run this.
Thanks.AAABPC B AAABPC B AAABPC C AAABPC C AAACWI B AAACWI B AAAHHG B AAAHHG C AAAHYY B AAAHYY B AAAJXP B AAALNP B AAAOOI B AAAPVI B AAAPVI B AAAPVI C AAAPVI C Perhaps
=UNIQUE(FILTER(A1:A1000,COUNTIFS($A$1:$A$1000,$A$1:$A$1000,$B$1:$B$1000,"C")=0))
