Multiple Value with different status on column

[Zakirahmed72]

I have a set of data where ID column can have duplicate values and status for the duplicate ID number can have a Pass , Fail and Epass status.  I need the output as in results field , any ID has a duplicate value with status as Pass and epass OR pass and fail, all other ID status need to set as fail. 

ID  Status  Results

1  Pass    Fail

1  EPASS  Fail

2  Fail     Fail

5  Pass   Fail

5  Fail     Fail

4  Pass   Pass

3  Fail    Fail

4 EPASS  Fail

1 EPASS Fail

[nagakavyasri]

Could you elaborate this sentence ' any ID has a duplicate value with status as Pass and epass OR pass and fail, all other ID status need to set as fail. '

This is confusing.

[Zakirahmed72]

If ID is repetitive than condition should apply on Status column (For example ; under ID, 1 is repeated trice and status is Pass, EPASS and EPASS , result should throw as Fail , any repetitive ID with status as Pass with EPASS or Fail needs to turn all repetitive ID status as "Fail" )

ID	Status	Results
1	Pass	Fail
1	EPASS	Fail
2	Fail	Fail
5	Pass	Fail
5	Fail	Fail
4	Pass	Fail
3	Fail	Fail
4	EPASS	Fail
1	EPASS	Fail
6	Pass	Pass

[FinnCharlton]

Hi @Zakirahmed72 , after reading your explanation, I would expect ID 4 to result in a 'Fail' for both rows, as it has a 'Pass' and an 'EPASS'. However one row results in a pass, the other in a fail. Is this right? Why is this different to ID 5 for example, where a 'Pass' and a 'Fail' both result in a 'Fail'?

[Zakirahmed72]

Hey @ FinnCharlton You are right, Thanks for highlighting it ! ID 4 to result in a 'Fail' for both rows, as it has a 'Pass' and an 'EPASS'. I have edited the question.  

[FinnCharlton]

Hi @Zakirahmed72 , here's how I would approach it. Let me know if the logic isn't quite right, had to make an educated guess for some scenarios: