In Column B I have "Job No" and in Column C I have "Open/Closed".

Col B Col C
1001 Y
1001 Y
1003 N
1003 Y
1005 N
1005 N


In Col A I need to create a function which goes through job nos and if
at least one has a "Y" then a Y is given, so the result of the above would be:

Col A Col B Col C
Y 1001 Y
1001 Y
1003 N
Y 1003 Y
1005 N
1005 N