Counting the output of workers over different shifts - pivot?

[Zaki]

Hi guys!

I have a problem I think I should solve with pivot, but don't know how to do it. I tried a little bit but have no clue ... I'm sure to get the right answer here!

I have a production facility with 2 shifts per day, Mo-Fr. On each shift are 2 Workers working together. They can be mixed in different teams. I want to know what the sum of the output per Worker is. I assume that, if 2 Workers work together in a shift, that they have the same output.

Based on the example below:
Worker A and B share the early Monday shift. So they made 1000 in this shift, everyone 500. So the sum of each worker is the sum of all shifts he worked in divided by 2. The problem I have is that the same worker can be in different coloums of the excel, based on the shift and the role (Worker 1 or 2).

The real excel is far more complex, with over 60 workers, 3 or 4 positions per shift, 2 or 3 shifts per day, with or without weekends etc ...

Thanks for your help!

 

[GraH - Guido]

When data can be normalized, the pivot is easy.
You can try the following Power Query script on your data.
Do Remember to update the source step. "Table3" is whatever the table name you give your data table. However, I would suggest to Get Data from File From Excel through the UI to keep my input and output separated.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Auto_types = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Early Shift", Int64.Type}, {"Worker 1", type text}, {"Worker 2", type text}, {"Late Shift", Int64.Type}, {"Worker 12", type text}, {"Worker 23", type text}}),
    Unpivot_all_but_day_col = Table.UnpivotOtherColumns(Auto_types, {"Day"}, "Attribute", "Worker"),
    Add_col_shifts = Table.AddColumn(Unpivot_all_but_day_col, "Shift", each if Value.Type([Worker]) = type number then [Attribute] else null,type text),
    Add_col_worked = Table.AddColumn(Add_col_shifts, "Worked", each if Value.Type([Worker]) = type number then [Worker] else null, type number),
    Fill_down_shifts_worked = Table.FillDown(Add_col_worked,{"Shift", "Worked"}),
    Filter_out_shift_attr = Table.SelectRows(Fill_down_shifts_worked, each [Attribute] <> [Shift]),
    Remove_attr = Table.RemoveColumns(Filter_out_shift_attr,{"Attribute"}),
    Group = Table.Group(Remove_attr, {"Day", "Shift"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Worked", each List.Min([Worked]), type number}, {"Sub", each _, type table [Day=nullable text, Worker=text, Shift=text, Worked=number]}}),
    Add_col_division = Table.AddColumn(Group, "Worked Each", each [Worked] / [Count], type number),
    Expand_sub = Table.ExpandTableColumn(Add_col_division, "Sub", {"Worker"}, {"Worker"}),
    Remove_helper_cols = Table.RemoveColumns(Expand_sub,{"Count", "Worked"})
in
    Remove_helper_cols

 

[bosco_yip]

Or, try a simple Sumproduct function formula way

1] Put criteria "worker name" in B9:B12 as per following "output table"

2] In C9, formula copied down:

=SUMPRODUCT((C$2:D$6=B9)*B$2:B$6+(F$2:G$6=B9)*E$2:E$6)/2

 

[GraH - Guido]

Or, try a simple Sumproduct function formula way

1] Put criteria "worker name" in B9:B12 as per following "output table"

2] In C9, formula copied down:

=SUMPRODUCT((C$2:D$6=B9)*B$2:B$6+(F$2:G$6=B9)*E$2:E$6)/2

View attachment 85010

Hi Bosco, I think you assume there are always 2 workers. While I understand there is variation: 3.or 4 positions per shift. Not sure your formula deals with that. Forgive me, I'm without excel so I can't test.

 

[bosco_yip]

Hi Bosco, I think you assume there are always 2 workers. While I understand there is variation: 3.or 4 positions per shift. Not sure your formula deals with that. Forgive me, I'm without excel so I can't test.

Hi GraH - Guido,
You are right, I always like short and sweet formula to solve question directly without any variation.
Cheers
Bosco