Search code examples
exceldaxexcel-2010

Check multiple rows for a unique combination across a whole table

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.

Solution

  • Well, this works:

    enter image description here

    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.