I have data as shown in the attached spreadsheet. I would like to show only single row in a table for each project based on the status. If the status is Completed for any of the project then I would like to show only the row with the Completed Status. If the status is not Completed for that project then second preferred row to select is In-Progress status row. If either of Completed or In-Progress is not there for any of the project, then I have to show the row for Void status.
I have attached the file with the input and desired output. Please assist with the expression. Listing down below my expression -
If( Status = 'Complete', Status, if ( Aggr (If( Status <> 'Complete' and Status = 'InProgress', Status ), Project, Project) = 'InProgress', Status, Aggr(If( Status <> 'Complete' and Status <> 'InProgress' and Status = 'Void', Status), Project, Project) ))
