Filter data by complex conditions.

**tezr** · 08-17-2010, 08:21 AM

Hi,

Any help on this data filtering problem I have would be much appreciated.

In the attached spreadsheet, I have data relating to 3 individuals. Column C shows their membership of four different groups “A, B, C and D”. You will see for every group that a person belongs to, a new row entry is created.

I want to be able to filter the data to show me only those individuals that are a member of A, B and C. I do not want the individuals name to appear in the filtered data if they are a member of D.

So in this example, the filter would present two records to me “Tomas” and “Harris” (or their Unique Applicant ID), because they belong to A and/or B and or C BUT NOT D.

Smith would be filtered out because although he is a member of A, B and C he is ALSO a member of D.

You will note it is possible for an individual to be a member of a group more than once. This is fine.

The real data sample has 11,000 entries. Help!

Thanks,
Terry.

**NBVC** · 08-17-2010, 08:26 AM

You can use a helper column in D..

In D2 enter formula: =COUNTIFS(B:B,B2,C:C,"D")>0 copied down

Then Filter for FALSE to get your results.

**tezr** · 08-17-2010, 08:34 AM

Genius. Thank you NBVC.

Is there a way to now filter down the (for example) four row entries that are displayed for Tomas to just one uniqe row entry? It doesn't matter which one of the four row entries excel displays (as I know that each row has a different group in it).

**NBVC** · 08-17-2010, 08:38 AM

How did I know that was coming

Try:

=AND(COUNTIF($B$2:B2,B2)=1,COUNTIFS(B:B,B2,C:C,"D")=0)

copied down and filter for TRUE this time.

**tezr** · 08-17-2010, 08:45 AM

Again, Thanks!

I'm going to push it once more and take advantage of your good nature...

If I want to extend the exclusion to include more groups. So for example, I don't want anyone who is a member of Group C OR D, how do I add that criteria into your formula?

If I try and add as below, I receive a too many conditions error:

=AND(COUNTIF($B$2:B2,B2)=1,COUNTIFS(B:B,B2,C:C,"D","C")=0)

**NBVC** · 08-17-2010, 08:48 AM

I think this should work:

=AND(COUNTIF($B$2:B2,B2)=1,SUM(COUNTIFS(B:B,B2,C:C,{"C","D"}))=0)

**tezr** · 08-17-2010, 08:51 AM

I think it does!

Thanks you've been a massive help.

Terry.

**NBVC** · 08-17-2010, 09:10 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**tezr** · 08-17-2010, 01:42 PM

Hi,

Apologies. I got dragged into a meeting and didn't get chance to mark as SOLVED. Thanks for doing this for me.

Thanks again for your help.
Terry.