Approach for automating excel with very large amount of data

Hi All,

I have a process where in a VLOOKUP between 2 different files(Both files are very huge: 50+ Columns and 200,000+ rows) on 15+ columns is to be done. The current approach to automate this that I have used is UI based automation. Usage of DataTables is not an option since the column names are not unique.

Current Approach:

  1. Select 5000 rows in loop and apply VLOOKUP formula to them. Batching is required as applying the formula to entire column takes around 15 mins and very high CPU utilization.

My queries are as follows:

  1. The machine configuration for both DEV and UAT is exactly SAME, still When the process is executing in UAT, the user faces delay issues and not in DEV. I have handled the delay issues by using element exists wherever possible. Any reason why?

  2. Is there a better approach to this process other than UI automation?

You could try to treat it as a DB:

How did you solve this issue?
Kindly share with me the approach as I am building a similar process.