Automated Data Aggregation and Formatting for Engineering Materials

Overview

This project involved creating an automation solution for engineers to compile and format data relating to engineering materials. The system is implemented as a VBA macro running within Microsoft Excel. The macro performs a range of tasks including data import from external CSV files, table creation, formatting, and deduplication.

Objectives

• Streamline the process of importing data from multiple CSV files into a centralized Excel worksheet.
• Automatically format imported data and create an Excel table for further analysis.
• Implement data deduplication methods to ensure data integrity.

Tools & Technologies

• Microsoft Excel
• VBA (Visual Basic for Applications)

Key Features

1. File Import: Allows users to select a CSV file for importing data.
2. Dynamic Table Creation: Automatically creates an Excel table to store imported data, with pre-set headers and formatting rules.
3. Auto-formatting: Automatically formats date and numeric fields, color-codes rows, and aligns text.
4. Data Deduplication: Searches for and removes duplicate rows based on the ‘Material ID’ column to maintain a single source of truth.
5. Error Handling: Includes checks for empty CSV files to prevent disruption in the existing table.

Challenges and Solutions

• Efficiency: Initially, the macro was slower for larger data sets. Optimized the VBA code to enhance the performance.
• Data Integrity: Ensured data deduplication to keep only unique entries in the dataset.
• User Error: Implemented error checks for cases such as empty CSV files to prevent undesired output.

Outcome

The automated solution significantly reduced the manual effort involved in compiling, formatting, and deduplicating large datasets. It enabled engineers to focus on data analysis rather than the cumbersome task of data preparation.