Get data from Multiple Excel Files through Cross-Referencing

Help
, ,
1

Hey guys,

I need to fetch data from one column of an excel file through cross-referencing another column in another excel file.

I need to search for the key column values from this excel
VTest.xlsx (9.4 KB) in the BILT column of this excel MTest.xlsx (9.0 KB) and search for the MATBB value referring that BILT value, and then extract that MATBB value and insert it to the corresponding key in the MAP column.

Thanks

2

Hi
Will these excels be like having empty columns in between
Cheers @NiranjanKN

3

No, they’ll be having some different data, and there’ll be lakhs of records.

4

Fine
because of empty columns inbetween we arent able to processit
the actuall process will be like this
–use excel application scope and pass the file path asinput
–inside the scope use read range activity and get the output with a variable of type datatable named dt1
–now use another excel application scope and repeat the same for the second excel file
and get the output datatable as dt2
–now use a FOR EACH ROW activity and pass dt1 as input and inside the loop use LOOKUP DATATABLE ACTIVITY where mention these in property panel
input = row(“yourcolumnname”).ToString
datatable = dt2
lookup column name = “your columnname in dt2 taken as lookup”
out result = str_output //a string variable
target column name = “your columnname in the dt2 whose value will be taken as output”

then followed by this lookup datatable activity next use WRITE CELL activity within the for each row loop with input value as str_output and range as
“R”+(dt1.Rows.IndexOf(row)+2).ToString and
in sheet mention the sheetname

hope this would help you
Cheers @NiranjanKN

5

Hello @Palaniyappan
Is there any Vlookup Formula that can be used, to process this.

Thanks

6

yah its there like how we use it in excel
–but for that inside the for each row loop with dt1 as input
use write cell activity and mention the formula as a string
Cheers @NiranjanKN

7

Hello, @Palaniyappan
I’ve tried your code Sequence.xaml (11.4 KB), but there’s a lil error occuring in it.Please do review it once.

Thanks

8

Fine
i tried to simulate this before commenting and if we are trying with the excel attached in the post, then we will be facing issue buddy
as there are empty columns in between the columns…
thats why i had a query on that

we need to have the column headers atleast in those empty columns if so it will work

Cheers @NiranjanKN

9

Hey,
I’ve updated the Excel, just give a suggestion.

Thanks