Project Planning Migrate Raw data into multiple sheets for ease of use

[Simplemountain]

Hello all,

I apologize because I know that most of my questions have been answered at some point. I have dug through countless posts about vba and standard excel solutions. However, the answers do not exactly apply to my current request. I have decided to ask, not for detailed solutions, but rather a project planning overview of how the task might best be achieved from beginning to end.

Here is what I am looking at:
I have a set of raw data that is generated into an excel sheet from an external program. The data represents about 1000 items that need calibration at different intervals. Due to the extent of the data, I am trying to break it down into departments to make the tracking easier. The raw data report comes in as a mess, but the relevant information is that it includes one column (column "H") of calibration due dates and one column (Column "L") that will eventually represent the department it belongs to. Assigning departments (Represented as the value of each row in column "L") to each item is a task that will be on going over time.

What I am looking for:
I would like to use the raw data to generate a unique sheet for each department as represented in column "L". The new sheets will each be in the same generic format that has a few rows of description and then a table which includes the data that belongs to that department. The format of the table will be identical to the Master sheet so that each column is the same information but only the rows of data that belong to that department are shown. I would like to create a way to migrate the data from the 'Master' sheet into the department sheets using the department column 'L'. The department sheets will contain conditional formating to color the calibration due dates (Column 'H') in order to alert the owner of that department which items are coming due for calibration. Until each item is assigned a department, I need an additional sheet for 'unassigned' items in column 'L' that do not fit in a known department value. This includes any value in column "L" that is not a currently defined department as well as blank cells. This sheet will represent any item not currently assigned to a specified department. The reason for a table is to be able to re-organzie the data on the end level to search for any relevant column of information needed.

Possible solutions:
Currently, I run a macro on the raw data to first clean up the blank rows and columns. I then copy the data into a table on a new sheet and am attempting to use the filter tools of tables to generate the sheets. I have also tried macros which work to populate new sheets but I cannot get it to work for populating tables.

Request:
I will be working on this for some time but thought it might be worth the time to throw out what I am trying to achieve and see if some of the experts might have a better Project Planning solution.

This board is amazing, thank you so much to everyone who takes the time to answer these questions and help educate those of us who are not so adept at excel and VBA. Thanks in advance to all of you who have taken the time to read this and might have possible solutions.

 

[JLGWhiz]

If you are working with PME on a time compliance certification schedule, I would suggest that you use a data base application like Access rather than Excel. It would provide you with all the tools you need to manage a repair and return operation based on time compliance. However, you can do it in Excel. Excel just requires a little more manual effort than if you had a data base program to handle the recalls and tracking.

Just an opinion. Regards, JLG

 

[Simplemountain]

JLGWhiz,

Thank You! That's exactly why I came here. I'm not familiar with PME or Microsoft Access but will certainly look into it. Can you be a little more specific on what you mean? I'm picturing email notifications when calibrations come due which would be 100% better than what I was currently looking at.

 

[JLGWhiz]

JLGWhiz,

Thank You! That's exactly why I came here. I'm not familiar with PME or Microsoft Access but will certainly look into it. Can you be a little more specific on what you mean? I'm picturing email notifications when calibrations come due which would be 100% better than what I was currently looking at.

PME is Precision Measuring Equipment (test equipment) which requires periodic maintence, calibration and certification. Access, as you noted, is a Microsoft database application which is more suitable for multiuser input/output operation, report generation and time sensitive tracking capability. Of course, you would need to have someone who can design your database and program your requirements. There is a discussion Forum on this MrExcel site where you could probably get much better input on your project than in this forum.

 

[Simplemountain]

I am very grateful for your response. I think you have presented the long term solution. I will still need to move forward with the excel sheets for the short term but after doing some research I completely agree that Access is the correct long term solution and that I should employ a professional to lay the ground work. Thank you very much for taking the time to respond. Sometimes we just need someone to take a look from a wider perspective.