Identifying all same duplicate ID's which also have one special identifier in only one row in another column

[Straycat1]

I am trying to achieve the following through formula for a largish worksheet (100,000 rows) (Excel 2010), to identify 'Special' process against all rows which have both duplicate Job ID's and one 'Special' process included:

Job ID	Process	Associated Special Process?
45001	Process 9	TRUE
45001	Process 3	TRUE
45001	Process 8	TRUE
45001	Special	TRUE
45822	Process 5	FALSE
46204	Process 5	FALSE
46204	Process 6	FALSE
46319	Process 3	TRUE
46319	Special	TRUE

<tbody>
</tbody>

I have been trying to use a combination of COUNTIF, MATCH and INDEX without any success, such as the following:

=(AND(COUNTIF(A:A,A2)>1,SUMPRODUCT(COUNTIF(B:B,"Special"))) which does not work, of course, but I don't know how to reference only the duplicate ID row's within this. Can anyone help?

 

[Eric W]

Welcome to the Board!

Try in C2:

=COUNTIFS(A:A,A2,B:B,"Special")>0

 

[Special-K99]

What would your output be?

45001
46319

Because those IDs have a Process named Special AND other rows that arent Special ?
Do we want IDs if there is only a Special row, ie no other non Special rows ?
What happens if a row has more than one Special ? Do we want that ID ?

 

[Straycat1]

Hi Eric,
That is perfect! Thank you very much.
Best regards,
Straycat1

 

[Straycat1]

Hi Special K-99,
Thank you for your response. Eric's solution works perfectly for what I need. To show that all rows of a duplicate ID's where one row has a Special process (there is never more than one Special for a single ID#) can be identified as associated with Special process. I can then pivot to show which processes also have a Special associated with them. Best regards, Straycat1

 

[Eric W]

Glad we could help.