Summarising data help (with pictures)

Gareth246

New Member
Joined
Aug 20, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
E7FBF5E9-1DEA-48AA-89FF-6B32E8C4F279.png
96217484-825C-4818-8D31-7BBBA4F4BC00.png

9EE4CEE6-BE20-408F-B965-2E5D83E9BFA5.png

I'm stuck on the best way to summarize date. I have 30+ Techs, they each have their own workbook set out as per, "User Workbook Example"

Each tech may do a different job each day.. they may do the same, however in there workbook the jobs are laid out as tabs at the bottom.

My Example here is for Brake fluid. The tech goes to the tab that describes the job they are doing, they then scan a bar code at the beginning of a job and they scan the same bar code at the end, both of these scans are time and date stamped, two scans equals a job complete.

I have then opened a new workbook and ran multiple power quieres. One for each job, i.e. break fluid as per my picture, "Power Query Example"

What I am trying to archive is a summary as set out in my photo, "Desired Result"

To do this I'm guessing a formula would have to automatically pull names from the query. my problem also is that I don't just want to see the average time they are on a job I want to see the average time based on their first clocking at for example at 6 am and their last clock say 5 pm divided by the amount of jobs they did that day. As They could be spending a lot of time not clocked on to a job which would falsely make their averages seem ok.

Does anyone have any ideas I'm going insane here !!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,299
Office Version
  1. 365
Platform
  1. Windows
Hi there,
What you could do: in PowerQuery, based on your first dataset. Make a new query and group by Name & Date Only. Next: calculate the sum of Column 8 (nr of minutes worked clocked that day), take the minimum of Start Time and the Maximum of End Time so you have a rough estimate of the time worked that day, you could add a count for any column to see the total number of jobs done that day. You could filter for "0-minute jobs" as they might skew your outcome and/or put in e.g. Maximum for Column 8 to see what the biggest outlier was.
The working hours fail if people regularly go out to e.g. see the dentist for 2 hours during their working hours. This link can help for the PQ: Power Query - Grouping and Summarizing Data - Excel Off The Grid
Cheers,
Koen
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top