Collating weekly data in excel (or another program)

rossi789

New Member
Joined
Jul 30, 2015
Messages
31
Hi all,

My company collects production data in weekly workbooks, with 1 worksheet per day. In the past there has been little emphasis on using this data beyond reviewing the day's output, however I have been able to demonstrate the benefits of combining the weekly data to analyse performance at a product, or shift level over a longer period. The issue I have is that currently, I am the only one who can do this. This is partly due to my own inability to automate combining the data so someone else can do it, and a lack of skills in my colleagues. However I am sure there is a much better way we can structure the data in the first place that would make things easier, so I am looking for suggestions on how to do that.

The Structure
As I have said, the information is collected in an Excel workbook on a weekly basis. The workbook contains 1 sheet per day and each sheet contains a table with the same headings.

The headings are: Date, Production Line, Product Code, Product Description, Units, Waste Units, Start Time, End Time, Run Time (calculated), Shift, Staff Used.

The workbooks are stored in a folder structure of: "\Year\Month\".

Data input
The data is input live during the day, most of it is manual with the exception of calculated columns and product description.

Combining the data
I have combined the data using power query within Excel to combine all worksheets and workbooks from the monthly folder into a monthly table and saved as a monthly workbook. Then the same process to combine those monthly tables into a table of all existing production data. I then use this as the basis for my analysis.

Aims
My use of power query is completely self taught with the use of Google so I am aware there are probably a multitude of mistakes in this process but that is why I am here. Ideally I need a solution which will allow someone with basic excel/IT skills to combine this data together.

I am open to any suggestions from completely redesigning how the data is collected in the first place to combining it through a more efficient or simpler method. We do have MS Access but it is not used, again due to lack of skills. Personally I have no experience with it but if it would provide a more flexible and robust data collection process then I can learn.

Thanks in advance for your suggestions!
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi Rossi,

what data do you collate in your monthly workbook? Just a compilation of the daily outputs? Or do you combine data in a smart way? Some small examples of the input (daily sheet) and output (monthly sheet) will help to get an idea.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top