How to change outcome of all the rows with same value based on one row outcome

[srikant]

I want to create a column changes the outcome of all the rows with the same value based on the outcome of one of the values. Example if 123 occurs 3 times once with the value "pass" then with value "on the edge" third time with the value "fail" then all three occurrences should be made "fail".

The priority should be

1)fail

2)on the edge

Please help its urgent. Thank you

3)pass

[Ashish_Mathur · Accepted solution]

Hi,

Assuming there can only be Pass, Fail and Over the Edge in the Individual result column, write this calculated column formula

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Individual result]="fail"))>0,"Fail",if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Individual result]="on the edge"))>0,"On the Edge","Pass"))

Hope this helps.