How to do vlookup one excel to another excel using linq

Help Activities
1

Hi Team,

I have 2 excel i want to do vlookup , Based excel 1 xxx column and excel 2 mmmm column to get Total amount

Please help me anyone for this.

excel 1:
image

Excel 2:
This is a table with four columns labeled "kkk," "mmmm," "Total Amd," and "lll," containing various alphanumeric values in each cell. (Captioned by AI)

Expeceted output:

please add sheet in excel 2 sheet name is “Vlookup” and update the vlookup result and removed N/A as well.

image

Regards,
Raja G

2

Have a look at this.

3

Hi @Raja.G

The image shows three Excel spreadsheets named "Sheet1", "Sheet2", and "result", each containing different sets of data in a tabular format. (Captioned by AI)
The image shows three Excel spreadsheets named "Sheet1", "Sheet2", and "result", each containing different sets of data in a tabular format. (Captioned by AI)1045×317 15.8 KB

The image shows a UiPath automation workflow where a "Build Data Table" activity is being configured to create a data table with fields "kkk," "mmmm," "total," and "description," and is assigned to the output variable "result_dt." (Captioned by AI)
The image shows a UiPath automation workflow where a "Build Data Table" activity is being configured to create a data table with fields "kkk," "mmmm," "total," and "description," and is assigned to the output variable "result_dt." (Captioned by AI)1175×823 59 KB

This image shows a flowchart from an automation process, with a conditional "If" statement checking if a row contains a specific item value, followed by a sequence of actions including writing a line, assigning values to data table rows, and adding the data row to a result data table. (Captioned by AI)
This image shows a flowchart from an automation process, with a conditional "If" statement checking if a row contains a specific item value, followed by a sequence of actions including writing a line, assigning values to data table rows, and adding the data row to a result data table. (Captioned by AI)386×762 34.5 KB

The image shows a table with columns for variable names, types, and scopes, highlighting that "ReportDT_Row" is of the "DataRow" type. (Captioned by AI)
The image shows a table with columns for variable names, types, and scopes, highlighting that "ReportDT_Row" is of the "DataRow" type. (Captioned by AI)901×173 8.83 KB

The image depicts a sequence of actions in a UiPath workflow, including assigning a value, adding a data row, and writing the data to an Excel file within an Excel Application Scope. (Captioned by AI)
The image depicts a sequence of actions in a UiPath workflow, including assigning a value, adding a data row, and writing the data to an Excel file within an Excel Application Scope. (Captioned by AI)376×621 22.7 KB

project.json (1.7 KB)
Main.xaml (20.3 KB)
Data.xlsx (10.3 KB)
Processing: image.png…

row("xxx").ToString.Contains(item("mmmm").ToString)
1 Like
4

Hie @Raja.G for this kindly refer to this logic this will solve your Query
your process look like this
This image displays an Excel Application Scope workflow in UiPath, where data is read from "Sheet2" and "Sheet3," assigned to a data table, and then written to the "JoinOutput" sheet before closing the workbook. (Captioned by AI)e

your linq query

This image shows an expression editor in UiPath being used to perform a join operation on two data tables and assign the results to a new data table. (Captioned by AI)
This image shows an expression editor in UiPath being used to perform a join operation on two data tables and assign the results to a new data table. (Captioned by AI)741×461 25.5 KB

Here dt_2 is first sheet thats hold the data and dt_3 holds the another data and dt_output holds final result data of matching sheets data
customize the column name as per your requirements

(
From a In dt_2
Join b In dt_3
On a(“Sno”).tostring Equals b(“Sno”).tostring
Select dt_Output.Rows.Add ({a(“Sno”),a(“City”),b(“ZipCode”)})
).copytodatatable

cheers Happy Automation

1 Like
5

@Raja.G Did you check?

6

Hi @lrtetala ,

Please help me on this.

Regards,
Raja G

7

Hi Team,

I have a solution but i dont know what column name found in excel , here i added column in manually , please help me how to pull all the column name automatically,

{From row1 in dt1.AsEnumerable()
Join row2 dt2.AsEnumerable()
On row1(“xxx”).Tostring().Equals row2(“aaa”).ToString()
Select dt2.clone.LoadDataRow(New Object(){
row2("Column1),row2(Column2), row1(“bbb”), row2(Column4)…}, False)).CopyToDatatable

Common value Reference number for both datatable , i want to get dt1 total amount to dt2

There is more column name found in between vlookup data also should update that one dt1 so i want to automatically update please help me anyone for this.

Regards,
Raja G

8

Common value Reference number for both datatable , i want to get dt1 total amount to dt2

9

@Raja.G Hi,

Can you try row names like row(0) row(1)

10

@Raja.G have a look at this