So I need the following to formula please, this is based of the following example (Column D needs to be determined from Columns A, B & C)
The formula needs to search down all of these and consider whats in each column based on The Course column (Column A) where that has occured more than once.
IF the course has occurred once meaning its a unique value in Column A then "Up to date" needs to be returned and thats regardless of the "Result" column
If the course has occured more than once and all outcomes are "Complete" (Course8 in the table) then whereever the lowest value in Column C, Column D needs to return "Up to date" and the highest value from column C needs to be "Ignore" for Column D.
If the course has occured more than once and all outcomes are "Outstanding" (Course7 in the table) then whereever the lowest value in Column C, Column D needs to return "Up to date" and the highest value from column C needs to be "Ignore" for Column D.
If the course has occured more than once and there is a mixture of "Outstanding" and "Complete" next to the relevant courses ( in this example Course1, Course2, Course3 and Course4, What ever has "Complete" next to relevant course Column D needs to return as "Up to date" or else "Ignore". But, for Course3 there is 2 completed and 1 outstanding, in this case I need the "Complete" course with the lowest value in Column C to return "Up to date" in column d, where as the Course3 entry that as has "Complete" but with the higher value in Column C that needs to be "Ignore" and ofcourse with the Outstanding one for Course3 that just needs to return "Ignore".
I hope I have explained what I am trying to achieve on this, I have been trying around for some time now but got nowhere, hopefully someone out there can help me achieve my goal. Many thank you.
Accepted Solutions