Can someone create process for Vlookup two excel files attached and send me .xaml file? Files excel.7z (171.1 KB)
I have been struggling with this issue several days and still it is not working.
Column from second excel file should be Vlookuped to first like this:
I created similar process, but I need to write cell values from File2.xlsx into the new column of FIle1.xlsx taking only those rows that exists in both file. How can I do it? I saw a lot of tutorials in web but could not find it.
use read range to read the File2.xlsx file and store in dt1
use read range to read the File1.xlsx file and store in dt2
use for each row for looping through each row of dt1, Here in for each row property panel there is field called index, it will store the index of row of datatable through which u are looping. in that create a variable which store the index of iterating row , lets say rowindex
inside for each row use lookup datatable activitiy with following parameter in the property ,
a) lookup datatable = dt2
b) lookupvalue = row(0).ToString
b) lookup column index = 0
c) rowindex = index ( an int32 varible which stores the rowindex of that lookupvalue in the lookup datatable
then after lookup datatable use an if Condition, with condition as,
index = -1, if index equal -1 then it indicates that there is no value in the dt2 which we are searching , so in then section of if activitiy , do nothing
While if index is not equal to -1 then the lookup value is there in dt2, then in else condition we need to use write cell activitiy with path of File1.xlsx and cell as βBβ+(rowindex+2).ToString ,
Hi @NIVED_NAMBIAR
Thank you very much for you responce!
I created process as you described and it writes result in Excel, but only first found item from File2.xlsx to File1.xlsx in the range of cells:
The screen of result in Excel file above shows that found matched item: B0047 written only to βB10β cell, however it should be written into the range of βB11:B20β. Also found matched item B0066 is written into the cell βB21β only, however it also should be written in the range of cells βB22:B34β, can you please help how to do it?
Hi @Apple1,
I think instead of index to write the cell value, u need to use index value of row through which it need to iterating,
Kindly check my above post i had updated it.
@NIVED_NAMBIAR
I changed Write cell Activity range to βBβ+(rowindex+2).ToString and it is showing an error: βrowindexβ is not declared. It may be inaccessible due to its protection level.
Where can I declare rowindex?
Thank you @NIVED_NAMBIAR
It works!
Process takes 40 minutes for execution on my side. What time did it take on your side?
Are there ways to make it work faster?