Compare two columns from 2 different sheets

  1. There is Excel1( Sheet 1 & Sheet2).
  2. Cell 1(Column1) of sheet 2 needs to be compared with all values in column1 of Sheet 1.
  3. If the value matches, copy the values of column2&3 of matching row from sheet1 and paste it in Column2&3 of sheet2.
  4. This way all the cells of column1 of sheet 2 need to be compared with column1 of sheet1 .

Attaching the excel for more clear reference.
test.xlsx (9.8 KB)

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.

@ayushi_jain3

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

Hi @ayushi_jain3

Please find the below xaml i have done the process as per your requirement.
BlankProcess1.zip (137.7 KB)


Hope it works!!

Hey @ayushi_jain3

(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:-

  1. 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

  2. This query should be put in the right side of the assign query and the left will contain the result datatable

Hi @vrdabberu thanks for the solution but I am expecting this kind of output
image

Hey @ayushi_jain3
This works as you requested
Please find the xaml in the below attachment

tests.zip (10.7 KB)

Thankyou @Vikas_M . It worked

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.