Exceptions formula

[propilot81]

I have a data set where I am trying to create a formula for each work order that does not have a specific task ("Task 5" Below Table) within the work order based on specific customers that must have this task with their work. There will be customers that do not apply and will not need this task performed. I am trying to create the formula in column D.
As seen in the example below, customer A1111 must have Task 5 in their work order. In work order 111111, Task 5 is present and work order is OK, however, in work order 333333 Task 5 is not present for customer A1111 and needs to be flagged "Need Task 5." Customer B1111 is a customer that does not apply.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Task[/TD]
[TD]Work Order[/TD]
[TD]Customer #[/TD]
[TD]Exceptions[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD]Task 5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]111111[/TD]
[TD]A1111[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD]Task 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]111111[/TD]
[TD]A1111[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]111111[/TD]
[TD]A1111[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]Task 1[/TD]
[TD]222222[/TD]
[TD]B1111[/TD]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]222222[/TD]
[TD]B1111[/TD]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]Task 1[/TD]
[TD]333333[/TD]
[TD]C1111[/TD]
[TD]Need Task 5[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]333333[/TD]
[TD]C1111[/TD]
[TD]Need Task 5[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

do you mean

however, in work order 333333 Task 5 is not present for customer A1111

should that read Customer C1111 ?
How would excel know that B1111 does not apply

you could use a countIFS()

in column D

 

[etaf]

=COUNTIFS(C:C,C2,B:B,B2,A:A,"task 5")
and copy down
That can be combined with an IF()

=IF(COUNTIFS(C:C,C2,B:B,B2,A:A,"task 5"),"OK","Need Task 5")
BUT , i dont know how to identify Not Applicable

 

[propilot81]

Wayne,

My apologies, customer should read A1111 for work order 333333. This customer needs "Task 5" on any work order created.

 

[etaf]

so not C1111
It will still work

but how does excel know "Not Applicable"

 

[propilot81]

so not C1111
It will still work

but how does excel know "Not Applicable"

That's one challenge I am trying to figure out. The majority of the customer's will be "Not Applicable," so maybe an if statement would help. There are only a handful of customers that need to have "Task 5" with each new work order generated under their customer number.

 

[propilot81]

That's one challenge I am trying to figure out. The majority of the customer's will be "Not Applicable," so maybe an if statement would help. There are only a handful of customers that need to have "Task 5" with each new work order generated under their customer number.

Wayne,

I made a couple of changes to the formula and set up the customers with a "Task 5" requirement in a separate table in column "I". This formula is working. Thanks for all the help!

=IF(ISERROR(MATCH(C2,I:I,0)),"Not Applicable",IF(COUNTIFS(C:C,IF(MATCH(C2,I:I,0)>=1,C2),B:B,B2,A:A,"task 5"),"OK","Need Task 5"))