Vlookup Excel hotkeys

excel
uiautomation
activities

#1

Hi All,

Does someone was able to manage a VLOOKUP in excel using hotkeys / desktop recording ?

I have to compare columns between two sheets, and retrieve a number to paste somewhere. I am doing two for each activities to iterate through the two sheet and then a if activity to compare results and write in a cell. The catch is I have 2000 entries in one sheet and about 1500 in the other, so UiPath crashes all the time. But running the process with a fair amount of data, still slow but working.

So back to my question, do you think it is possible, if so how ? Will it significantly improve the process ?

Many thanks in advance for your inputs…


#2

Hello, yeah for whatever reason their For loops are super slow and blows up your memory.

You should do some research on filtering or comparing datatables in vb.net and LINQ expressions on Google or this forums.

One method would be to use 1 ForEach, then inside use .Select to retrieve the value from the other table.
For example,
ForEach row1 in dt1
Assign variable = dt2.AsEnumberable().Where(Function(row2) row2(column1).ToString = row1(column1).ToString).ToArray(0)(column2).ToString
WriteCell with the variable

This is just an example but hope it helps. Also, I have not tested above function and could have a syntax error.

Alternatively, you could also place an Excel formula in the first cell then use Select Range and Ctrl+d hotkey to Filldown using TypeInto “[d(ctrl)]d[u(ctrl)]”

Regards.


#3

Thanks for your quick reply ClaytonM !

I will try your second option first. I’ll keep you posted and post the working solution here (If I find one)

Regards,


#4

Total Matched Count.zip (11.5 KB)

You can check it.
Change paths inside xaml as per your folder structure.

I have tried to solve issue in different way by using 1 extra formula file.
As of now it will match around 100 rows. You can update it as per your requirement.
Please let me know if you need any help.