Situation
The ETL process to upload the pricing into the system was manual, lengthy and with a high rate of inaccuracies. It used to take 4 hours or more to process dozens of files, and prices would be regularly changed during that timeline.
Task
I took the initiative to automate the process by using macros to perform all manual steps, thus saving processing time and giving the buying team the opportunity to make last minute changes without delaying the delivery of the CSV files to IT.
Action
The process was a monotonous loop of copying, pasting, formatting, manual checking, and exporting at least 12 Excel files. A macro was created to open up to three files in a loop, process them and exporting the CSV files to be sent to IT for upload to the AS400.
A master sheet would reconcile the imported/exported data in order to find any discrepancies before sending the data to IT for upload.
One of the challenges to address was the tendency of the buying team to change the structure of the PTS (Plan to Sell) files. This would cause the macro to copy/paste data in wrong places. The solution to this was to find header values and set columns/rows based on that search.
Results
After significant decrease in processing time, from several hours in less than 3 minutes, last minute changes were not of further concern and accuracy was increased significantly in flyers and signing data upload.
Later, a new IBM pricing system was proposed and when the time of processing was determined to be as lengthy as before this project, thus management pushed back pointing out that the macro should be kept or another, more efficient process should be implemented.
Technical Data
File Dialogs to get file paths.
If statements to check for pricing discrepancies.
Index+Match to lookup pricing in any direction.
Excel
Visual Basic