Check multiple rows for a unique combination across a whole table

Question

I'm using Excel 2016 and try to filter a simple table based on values across all rows and columns. Let's assume the following table:

ID	Customer	Work
1	Nancy	Inspection
2	Peter	Inspection
3	Peter	Inspection
4	Anna	Inspection
5	Nancy	Tire Change
6	Anna	Inspection
7	Tom	Tire Change
8	Anna	Tire Change
9	Anna	Tire Change

I want to know only the customers, that had only an inspection. As soon as they had a tire change in addition to an inspection or only a tire change (like Tom), they should be filtered out.

So the result should look like this:

ID	Customer	Work
2	Peter	Inspection
3	Peter	Inspection

Peter is the only one left, because across all rows he only had an inspection and nothing else.

I tried multiple ways for filtering duplicates and different logics, but it seems it won't be solvable without a vlookup or similar, to which I'm not used to.

Answer 1

Well, this works:

In cell F2;

=UNIQUE($B$2:$B$10)

In cell G2:

=IF(COUNTIFS($B$2:$B$10,F2,$C$2:$C$10,"Inspection")-COUNTIFS($B$2:$B$10,F2,$C$2:$C$10,"Tire Change")>=1,1,"")

In cell A15:

=IFERROR(INDEX($F$2:$F$5,MATCH(B15,$G$2:$G$5,0)),"")

In cell B15:

=IFERROR(LARGE($G$2:$G$5,1),"")

So edit it as you wish. You can add another index() with match() to get the ID...

Deconstructing the formulae will help you understand how it works.

Answer 2

You can use this one formula (pr column) if you have excel 365 (insider not needed):

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,IF(COUNTIFS($B2:$B10,$B2:$B10,$C2:$C10,"<>Inspection")=0,A2:A10,""))&"</s></t>","//s")

this formula is for column A and can be dragged to the right for the other columns