How to compare duplicate values in different columns and make a new array with unique values?

[Karlos90]

Greetings everyone.
I will do my best to explain my situation, I apologize for my grammar mistakes.
I have a problem with making an array functions for the two columns.
First one "OK"(column F), should return the unique values of "Product Serial Number"(column A) that have values: "OK" in the column E "OK/NOK" (column E). I managed to do something about that with INDEX;MATCH but the hard part for me is the next step. One unique value from the column A will be in the column F ("OK") only if, in this case, all of that values of that serial number have "OK" in the column E "OK/NOK". But if there is only one "NOK" then that serial number must not be in the column F("OK") but in the column G ("NOK").
To summarize, the column F needs to have the array with the unique serial numbers from the column A which have "OK" for all their values in the column E, and if they have only one "NOK" they need to be returned to the column G "NOK".

I have attached a file so you can see clearer what I am writing about.
I have read the rules of the forum, it is my first time here, and I apologize again for any kind of mistakes I have unintentionally made. Notify me if I have made them.
I really appreciate any help you can provide.

 

[Chihiro]

Are you opposed to having different layout for result? This type of analysis is quick and easy with PivotTable.

Of course, depending on your final operation/presentation requirement, this may not suite your need.

See attached for 2 versions (3 PivotTables).

1 & 2 used helper columns and applied different value filter (1 shows item where Sum of NOK =0, 2 shows item where Sum of NOK > 0). 3 without helper columns.

 

[Karlos90]

@Chihiro Thank you,
This will do the job, though I am very curious about the solution with an array functions. I'll try something soon when I learn more, and I'll post it here. Your solution is good for my needs and I am gratefull for your help, thank you again.

 

[Chihiro]

Array formula solution.

I'd use helper column with formula below:
=COUNTIFS($A$2:$A$61,A2,$C$2:$C$61,"bad",$D$2:$D$61,"after")

Which will return 1 if there is any NOK in for Serial#, 0 if all OK.

Then array formula in H2:
=INDEX($A$2:$A$61,MATCH(0,IF($F$2:$F$61=0,COUNTIF($H$1:$H1,$A$2:$A$61),""),0))

Array formula in I2:
=INDEX($A$2:$A$61,MATCH(0,IF($F$2:$F$61=1,COUNTIF($I$1:$I1,$A$2:$A$61),""),0))

Copy down until you see #N/A error.

 

[Karlos90]

This formula fits for my needs perfect! Thank you for your time and shared knowlegde, many thanks @Chihiro.