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

Help Activities
1

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
2

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
3

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)”

4

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
5

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

image
image795×733 19.8 KB

6

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

7

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
8

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

9

Yup sure

10

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 ?

11

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
12

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

1 Like
13

No its two different files @ImPratham45

14

Then Use the First One

15

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

16

Here MAIN is my sheet in other excel file

1 Like
17

[Adapters.xlsx] is your file name?

18

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

1 Like
19

image
image1746×741 73.7 KB

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

20

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

1 Like