Rank with conditions

viceview51 
One claification needed, what should be result when you have new employee code, should the result resets or it stays in same pattern.

I am assuming the result column sequence will reset and my suggested solution:  

Use Power Query that is more straight forward and easy to implment

Step 1 - Open Power Query

Home → Transform Data

Step 2 - Sort the data

Sort by

employee_ID

Date

Ascending.

Step 3 - Open Advanced Editor

You'll see something similar to:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
    Source

Replace it with the following code (change Table1 to your table name if needed).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {"employee_ID", type text},
                {"Date", type date},
                {"Code", Int64.Type}
            }),
    #"Sorted Rows" =
        Table.Sort(
            #"Changed Type",
            {
                {"employee_ID", Order.Ascending},
                {"Date", Order.Ascending}
            }),
    Records = Table.ToRecords(#"Sorted Rows"),
    ResultList =
        List.Accumulate(
            Records,
            {},
            (state,current)=>
            let
                CountRows = List.Count(state),
                Previous =
                    if CountRows=0 then
                        null
                    else
                        state{CountRows-1},
                NewResult =
                    if Previous=null then
                        7
                    else if current[employee_ID]<>Previous[employee_ID] then
                        7
                    else if current[Code]<>Previous[Code] then
                        7
                    else if Duration.Days(current[Date]-Previous[Date])<>1 then
                        7
                    else if Previous[Result]=1 then
                        7
                    else
                        Previous[Result]-1,
                NewRecord =
                    Record.AddField(current,"Result",NewResult)
            in
                state & {NewRecord}
        ),
    Output =
        Table.FromRecords(ResultList)
in
    Output

Hopefully, you will need some tweking and you will get the desired results.

If this helps, ✓ Mark as Kudos | Help Others

Proud to be a Super User