use 2 read range activities under excel application scope
in first read range activity read the first sheet and in second read range activity read the second sheet
store them in different datatable variables
then Add a “For Each Row” activity and set the input DataTable as sheet2 ka datatable
Then use curent Row(0) to get the value of first cell of sheet 2 datatable
Then add an an other for each activity and set the input as sheet1 ka datatable
Then add an If" activity to check if the currentrow(0) of sheet 2 datatable matches the value of the current row’s current rows column 1
If there is a match, use the “Excel Write Cell” activity to copy the values from Column2 and Column3 of the current row in Sheet1 and paste them into Column2 and Column3 of the corresponding row in Sheet2. To identify the corresponding row in Sheet2, you can use the
CurrentIndex property of the outer loop.
Excel Application Scope:
WorkbookPath: “Path_to_your_Excel_File.xlsx”
Read the range from “Sheet1” → Output: dtSheet1
Read the range from “Sheet2” → Output: dtSheet2
Assign activity:
Create a new DataTable dtResult with the same structure as dtSheet1 (columns: Column1, Column2, Column3)
For Each Row activity (row1) in dtSheet2:
For Each Row activity (row2) in dtSheet1:
If row1(“Column1”).ToString = row2(“Column1”).ToString:
Assign activity:
row1(“Column2”) = row2(“Column2”)
row1(“Column3”) = row2(“Column3”)
Break (to exit the inner loop once a match is found)
Write Range activity:
Sheet: “Sheet2”
Range: “A1” (or the starting cell of your choice)
DataTable: dtSheet2
(From d1 In dt1.AsEnumerable()
Group Join d2 In dt2.AsEnumerable()
On d1(0).ToString() Equals d2(0).ToString() Into serialGroup = Group
Let rowsNeeded = If(serialGroup.Any(),
d1.ItemArray.Take(dt1.Columns.Count - 2).Append(serialGroup.First()(2).ToString()).ToArray().Take(dt1.Columns.Count - 1).Append(serialGroup.First()(3).ToString()).ToArray(),
d1.ItemArray.Take(dt1.Columns.Count - 2).Append(String.Empty).ToArray().Take(dt1.Columns.Count - 1).Append(String.Empty).ToArray())
Select dt1.Clone().Rows.Add(rowsNeeded)).CopyToDataTable()
Note:-
The above code will first join excel 1 and excel 2 items if they match. Then we insert the values of datatable2 to the respective columns of datatable1 and if no match is found, then it is set to Nothing and the entire row is added to datatable
This query should be put in the right side of the assign query and the left will contain the result datatable