Data Compilation Using RPA for a Medical Device Company

Business Context

Our client, a major Medical Device company, had a tedious process to handle sales data received from customers. The files are received through mail. The files need to be downloaded, compiled into a consolidated file across customers, and then maintain the status of receipt.

Challenges

Our client receives several hundred files, and the same needs to be checked and consolidated. The challenges faced were:

1. -The time taken to download all the sheets from individual customers was high. Need to focus on email to download all the sheets from different vendors.
2. -All the Excel data sheets are password protected. There are two separate passwords for two kinds of formatted Excel sheets. Passwords need to be used to copy all the data, which consumes more time.
3. -Some of the columns need to be copied to the compilation file. When manually done, there is a possibility of error.
4. -Two resources’ time had to be allocated to this process, which could be utilized for other activities.
5. -Considering it is a repetitive task, it became quite tedious to handle.
6. -A status report also needs to be prepared on the data received, and when manually done, some of the updates were getting missed out.

Each of the above had to be handled by our team with great support from the client for a successful rollout. Our RPA Solution could solve these challenges.

Our Solution

To mitigate the challenges, we implemented Robotic Process Automation using UiPath. The steps being followed manually were fully automated. The steps involved were:

1. -Auto-download all the attachments from the respective emails.
2. -After separating all the files as type 1 and type 2, the bot will iterate through all the type 1 files, auto-fill the password, and read individual sheets.
3. -Copy data from all type 1 files and paste it into the compilation file in two respective sheets.
4. -Auto-save the file.
5. -The same process will be repeated for type 2 files, and data will be copied into the compilation file.
6. -After all the processes are done, the files present in the type 1 and type 2 folders are moved to a separate location as backup.
7. -As compilation is being done, the status update for receipt is also completed by the bot.

Benefits from our Solution

There are several benefits:

1. -Bots are faster. They follow the steps in the same order with no deviation or distraction.
2. -Bots work 24/7 with no break, so the number of bots needed is far less than the number of manpower required.
3. -Bots don’t experience fatigue. The repetitive task is performed with no mistakes.