Project in my current job

Introduction

In my current job i usually create a lot of reports, each one different according to the client requirements. One particular client has multiple ongoing projects with the company, which means I need to deliver a large number of reports each month. Since the company did not have any automated processes in place, I initially had to prepare these reports manually and with repetitive processes, which was tedious and time-consuming, often taking up to three hours per report. This client require the reports in Excel format, so I developed a macros that significantly improved reporting speed and reduced the preparation time to just one hour.

For confidentiality reasons, I cannot show the project step by step with the actual images, so I will try to recreate it and explain it as best as possible using images that allude to what I am referring to. I recommend reading a bit about my current work in its respective section, as it will give you more context about what I do on a daily basis and make it easier to understand this project.

Explanation of the project

In my current job, we perform a double cleaning and analysis process on the data we obtain. The first cleaning is more technical and less common in the field of data analysis, since we use company software to clean and analyze the data. However, this stage is only meant to detect anomalies and flag them, after which the software uploads the information into a database that includes distance, depth, and other relevant details.

Once the first analysis is completed and everything is validated, we can download two tables that are generated in the database (each one in its own Excel file ). These serve as the basis for further cleaning according to the client’s requirements, analyzing the data, and generating the report with the findings.

This is where I implemented my macro, so the following steps could be executed automatically instead of manually:

Step 1: Review Table 1 to determine which columns are necessary for the client and remove those that are not useful. (We cannot do this in SQL since the company has restrictions and only allows us to use predefined scripts to download the data.)

Step 2: Update certain feature names and values in the table, because some clients refer to features differently. For this specific client, we had to adjust some names and values accordingly.

Step 3: Use Table 2 to transfer specific values from one of its columns into Table 1. For this, we first needed to filter certain values in Table 2. To do that, i used VLOOKUP .

Step 4: Once the values from Table 2 were added to Table 1, we verified that the counts matched to ensure data accuracy.

Step 5: Transfer the modified Table 1 into the Excel report template. At this stage, we still had to make several adjustments to the template to guarantee proper formatting (adjusting headers, drawing borders, modifying logos, etc.).

Step 6: Count the anomalies and features in the table. With the result of this count, the value can be directly transferred into the report.

Step 7: Extract the information from Table 1 that is required for the report and copy it directly into the report.

Step 8: Adjust the final formatting of the report.

I summarized the entire reporting procedure into 8 steps, but it is longer. Next, I will show the structure I designed for my macros code. I divided it into 4 sections that must be executed individually, since in some procedures the user is required to manually enter information. Once the information is entered, the user can run the next section of the macros to continue the process.

These are the 4 sections and their respective procedures that they execute:

Project P1

' Select the correct Sheet.
' Add APJ column and format it.
' Delete columns after Comments.
' Delete unnecessary columns.
' Add margin.
' Replace names.
' Delete % of OD restriction for Geometric Anomalies.
' Delete POF Dimensions for MLMILL.
' Change cell size for Feature Type and Pipe Type.
' Filter to perform DCP step.

Project P2

' Use VLOOKUP formula with DCP Listing open.
' Select formula, copy and paste it into the entire column, then copy and paste values.
' Delete cells with N/A.

Project P3

' Table borders.
' Count chart.
' Count chart naming.
' Formulas for counting.
' Part 2 formulas.
' Part 3 formulas.
' Date reminder.
' Get MAX corrosion value.
' Get MAX MLMILL value.

Project P4

' Report filling.

With this macros, I was able to reduce the preparation time from 3 hours to just 1. In addition, I created variations of the macros, since the report changes depending on the client’s product and the inspection tool used. This makes all reports for this client faster to complete and helps avoid human errors in repetitive processes.

Page updated

Google Sites

Report abuse