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.
CodePudding user response：
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.
CodePudding user response：
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
