How to find rows in excel that that only match a given criteria across all rows where one specific field has duplicates

user_19496539 0
Viewed 27

If I have a table that looks like the below. How can I get excel to return the breed of dog that only meets the criteria Is_Brown = 0 and never meets the criteria Is_Brown = 1. For example in the case below that would be the Golden Retriever and Bulldog.

Dog Breed Is_Brown
Labrador 1
Spaniel 1
Poodle 0
Labrador 0
Bulldog 0
Labrador 1
Golden Retriever 0
Poodle 1
Spaniel 0
Golden Retriever 0
Bulldog 0

If I try to filter or use a pivot table and filter on Is_Brown = 0 this would still include those breeds where for that specific row the filter criteria was met but I want the Dog Breed where the criteria applied is ONLY ever met across all rows.

excel
2 Answers

For your specific example, you can use a pivot table with Breed as rows and Is_Brown as summed values.

enter image description here

Then the breeds you look for are the ones for which the sum is nil, you just filter the rows by value:

enter image description here

Which gives you the expected output:

enter image description here

user_43830320

If you are using Office 365, this should work (assuming breed is column A and is_brown is column B :

=FILTER(UNIQUE(A2:A12),SUMIF(A2:A12,UNIQUE(A2:A12),B2:B12)=0)

  • UNIQUE(A2:A12) : Returns an array of breeds without duplicates
  • SUMIF(A2:A12,UNIQUE(A2:A12),B2:B12) : Returns an array of the sum of "is_brown" for each breed
  • =FILTER(UNIQUE(A2:A12),SUMIF(A2:A12,UNIQUE(A2:A12),B2:B12)=0) : Returns the breed for witch the sum of is_brown is 0

If you are using a table this would also work (assuming your table is called Table1) : =FILTER(UNIQUE(Table1[Dog Breed]),SUMIF(Table1[Dog Breed],UNIQUE(Table1[Dog Breed]),Table1[Is_Brown])=0)

user_191793990
Related