EXTRACT UNIQUE LIST FROM TABLE THAT COMPLIES TO MULTIPLE CRITERIA
I have an Excel table named “DataTable” with the following headings:
Inc. ID
Description
Inc. Grp
Incident Priority
Inc. Plant
Inc. Status
Inc. Closed Date
Incident Owner Name
I use the following array formula to extract a unique list of “Incident Owner Names”:
{=IFERROR(INDEX(INDIRECT($B$2&"[Incident Owner Name]"), MATCH(0,COUNTIF($K$5:K5, INDIRECT($B$2&"[Incident Owner Name]")), 0)),"")}
Where cell B2 has the name of the table, i.e. “DataTable”.
The formula is placed in cell K6 and dragged down far enough to cover all names.
What is required is an array formula that excludes a fixed list of names, in this case:
TP Jacobs
CN Pieterse
BS Johnson
L Mcain
G Pelser
It must give a unique list of “Incident Owner Names”, where the following is true for at least one table entry, while excluding the above list:
Plant = “PLANT ONE”
Incident Priority – “001”
Inc. Status = “Closed”
This must be achieved without filtering or Pivot Tables.
Can this be done?
Best Regards,
Henk Stander
Bookmarks