Write a formula for table with duplicate values in Column A

I worked this one out with some helper columns.

First I converted the data into an Excel Tables because Excel tables know how big they are so when you add data, you do not have to change anything.

The helper columns are Negative, Positive and New Result

Negative =COUNTIFS([Order no.],[@[Order no.]],[RESULT],"Prod at fault ")+COUNTIFS([Order no.],[@[Order no.]],[RESULT],"Dispatch at Fault") which counts the negative findings.
Positive =COUNTIFS([Order no.],[@[Order no.]],[RESULT],"Normal")+COUNTIFS([Order no.],[@[Order no.]],[RESULT],"Dispatch good job") which counts the positive findings

New Result =IF(AND([@Negative]>0,[@Postive]>0),"Mixed",[@RESULT]) which is mixed if there is at least 1 negative and 1 positive - otherwise use the original result.

Hi, welcome to the forum

Here is a shortened vesrion of your formula...
=IF($C2="Good",IF($B2="Good","Normal","Dispatch at fault"),IF($B2="Good","Dispatch good job","Prod at fault "))

Now, you mention "has anyone of one the status "Dispatch at fault" or "produktion at fault" yest in your dropdown for the 2 status's, you only have Good or Bad?