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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Job ID[/TD]
[TD]Process[/TD]
[TD]Associated Special Process?[/TD]
[/TR]
[TR]
[TD]45001[/TD]
[TD]Process 9[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]45001[/TD]
[TD]Process 3[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]45001[/TD]
[TD]Process 8[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]45001[/TD]
[TD]Special[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]45822[/TD]
[TD]Process 5[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]46204[/TD]
[TD]Process 5[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]46204[/TD]
[TD]Process 6[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]46319[/TD]
[TD]Process 3[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]46319[/TD]
[TD]Special[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

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.