Using LookUp Data table Searching for data from 1st excel to fill 2nd excel column values

Hi Friends,

I have two different excel files. one file is master data file and another excel file is where the values to be filled by searching ID column in master data file. There are nearly 49k rows to be filled.

I have used LookUp data table activity and searching data from master file and filling data into the 2nd file. Since there are more than 49k rows in my 2nd excel file, the robot is taking longer time to fill the values into my 2nd file. It took nearly 30 minutes for the robot to fill the data for just 2100 rows. but a lot more rows(47k rows) to be filled in my 2nd excel file which will take more than an hour.

I request you to suggest me how to achieve this. (I am doing VlookUp activity)

Thanks in advance.
@Palaniyappan @balupad14

1 Like

You can try like this Use Write Cell at the start of file where you want to add that formula like this

Use write cell say here in E2

β€œ=VLOOKUP(A1,’[Adapters.xlsx]MAIN’!$C:$C,1,0)”

Then For count of rows use
Read Range for sheet say DT1
Then Use assign Count=DT1.Rows.Count+1

Then use Autofill Range
In that Give

source as β€œE2”
Destination as "E2:E:+Count.ToString

It will fill up Formula to your all rows in that column

1 Like

Thank you @ImPratham45.
May I know whether in the highlighted code below, A1 and $C are fixed ? β€œ=VLOOKUP(A1,’[Adapters.xlsx]MAIN’!$C:$C,1,0)”

No You can add there whatever you want by using string manipulation
Like this if you want to store that in variable then it will be
CellName=β€œB”
LookUpCol=β€œP”

β€œ=VLOOKUP("+CellName+β€œ1,’[Adapters.xlsx]MAIN’!"+LookUpCol+":”+LookUpCol+",1,0)”

I hope you get idea how to make it as per your need

1 Like

I am using the below flow @ImPratham45 I am looping it using for each row , that’s why I think it is taking longer time to fill the rows

I will try and get back to you on this @ImPratham45 once again Thank you very much :slight_smile:

Dont use For each now
Pls go with what i mentioned above it will directly apply on all cells just like when we copy paste formula directly on that range

1 Like

oh okay, Thank you @ImPratham45. I will apply it in my code and get back to you

Yup sure

two different excel sheets here, one is values to be filled and another one is values to search for and get. How will the robot know that from which excel sheet the lookUpCol is in ?

The sheet in which u need to write it will be in write cell : i.e. it will ask the sheet name for write cell and in value u will add that formula

1 Like

If the same file and two sheets then it will be like this
=VLOOKUP(A2,β€˜Sheet2’!A:C,2,0)

1 Like

No its two different files @ImPratham45

Then Use the First One

Any suggestions on this @ImPratham45 since I am using two different files, that’s why I have used
ForEachRow. because, i am using two different dataTables. taking a column from one datatable(dt1) and using lookup datatable and then searching value for that particular column in datatable2(dt2) and writing it into the first file excel column

Here MAIN is my sheet in other excel file

1 Like

[Adapters.xlsx] is your file name?

Here [Adapters.xlsx] different excel say master one You can give full path of that file
nd MAIN is sheet name

1 Like

filling the values as #N/A in the above destination column bro. the formula that I applied was
=VLOOKUP(E6,’[Article Master - Global.xlsx]Sheet1’!D:D,6,0)

searching the value of E6 from master data excel file using lookUp column of D in Master data excel.

May I know what is the wrong in the syntax @ImPratham45

formula applied to excel is correct
so pls check is your formula is correct or not
check using manually writing it

1 Like