propilot81

New Member
Joined
Apr 12, 2013
Messages
4
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.


Column AColumn BColumn CColumn D
TaskWork OrderCustomer #Exceptions
Task 5

<tbody>
</tbody>
111111A1111Ok
Task 1

<tbody>
</tbody>
111111A1111Ok
Task 2111111A1111Ok
Task 1222222B1111Not Applicable
Task 2222222B1111Not Applicable
Task 1333333C1111Need Task 5
Task 2333333C1111Need Task 5

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
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

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
=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

New Member
Joined
Apr 12, 2013
Messages
4
Wayne,

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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
so not C1111
It will still work

but how does excel know "Not Applicable"
 

propilot81

New Member
Joined
Apr 12, 2013
Messages
4
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

New Member
Joined
Apr 12, 2013
Messages
4
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,095,177
Messages
5,442,840
Members
405,201
Latest member
kashyap44

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top