Unique reference based on multiple columns

    Not the best topic total. I have a list of engineer repair jobs for which I require a formula to calculate which jobs were completed 1st time with no subsequent appointments. This means that the job must have an appointment result of completed and not unable.(Unable mean failure)The jobs can have multiple appointments with the same date as the completed date, this is a 1st time fix. However if the job has multiple appointment dates with different dates to the completed date this is a failure. I have attached the sheet with some examples of 1st time fixes and failures


    thanks
    Richard

    Maybe try


    Code
    =IF(F13="","",if(E13<>F13,"Failure","1st Time Fix"))


    in cell B13, then drag down


    Make sure that there are no cells in column B that are merged first though, otherwise you will get an error

    Thank you jl2509 this formula works fine line by line , however the unique job number (column C) often has more than one line. To complicate things further Appointment Results (Column M) needs to be added to the mix. Any unique job number (column C) with an Appointment Results (Column M) of 'Unable' is automatically a failure.


    Not any easy request I know

    Hi


    Forgive me but I am a bit lost now.
    How does column M get updated to "Completed " or "Unable"


    Is the result provided not the same as you are asking. i.e. the job completed date is not the same as the Appt Date so "Failure"
    if the appointment result = "Failure" then the reason surely must be either: not attended,More than 1 visit, a completed date greater than the appt date !


    This would be the same result?


    unless I am way off with my 2nd guessing that is!

    Change the formula to:

    Code
    =IF(F13="","",IF(M13="Unable","Failure",IF(E13<>F13,"Failure","1st Time Fix")))


    and drag down

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login