Advanced filter/expression to limit entries

Question

I need help with either an advanced filter or expression in either SAS EG (4.3) of Excel (2013)

I have a dataset with different breeds, father's country of origin and birth dates for pigs. I need to compare offspring born on the same date, for each breed, but entries must be limited to dates that include one international country and the birth country.

So for example.. If country B and C are international countries, for all born in January 2010, if breed 1 only has offspring from country A and breed 2 has offspring from both country A and C, only breed 2's entries will be shown for both countries. Also, there must always be one entry from country A.

So if the entries are:

Date Country Breed

Jan 2010 1 A

Jan 2010 1 A

Jan 2010 2 A

Jan 2010 2 C

Feb 2013 1 B

Feb 2013 1 B

I only want to only see

Date Breed Country

Jan 2010 2 A

Jan 2010 2 C

Any help will be greatly appreciated!!

Answer 1

Here's a possible Excel approach. Using COUNTIFS is just a way of seeing if there is at least one case in a group with same date and breed where the following conditions are satisfied.

(1) At least one with country A (in E2):-

=COUNTIFS($A$2:$A$7,"="&A2,$B$2:$B$7,"="&B2,$C$2:$C$7,"="&C2,$D$2:$D$7,"=A")

(2) At least one with a different country (in F2):-

=COUNTIFS($A$2:$A$7,"="&A2,$B$2:$B$7,"="&B2,$C$2:$C$7,"="&C2,$D$2:$D$7,"<>"&D2)

Then combining the two conditions (in G2):-

=AND(E2,F2)

then pull the formulae down and filter on column G.

You could also do it with an array formula but I think this is easier to understand.