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!
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:
