Compare 2 columns of two sheets , find unmatched rows, copy in different sheet and delete those rows in original workbook

  1. There is one workbook with 2 sheets.
  2. We need to compare both the sheets using first column of both sheets. For ex: cellA of sheet2 should be compared with whole columnA of sheet1.
  3. the unmatched data( unmatched rows) should be moved to a new sheet and deleted from original one .
  4. For the ones that matched, column2 and column3 should be copied from Sheet2 to column2 and column 3 of sheet1.

@ayushi_jain3

This gives unmatched

  1. unmatcheddt = Dt1.AsEnumerable.Where(function(x) Not dt2.AsEnumerable.Any(function(y) y(0).ToString.Equals(x(0).ToString))).CopyToDataTable

For matched

  1. matcheddt = Dt1.AsEnumerable.Where(function(x) dt2.AsEnumerable.Any(function(y) y(0).ToString.Equals(x(0).ToString))).CopyToDataTable
  2. Use this inside invoke code for updating column 2 and 3
    Matcheddt.AsEnumerable.ToList.ForEach(sub(r) r(1)=dt2.AsEnumerabl.Where(function(x) x(0).ToString.Equals(r(0).ToString))(0)(1).ToString r(2)=dt2.AsEnumerabl.Where(function(x) x(0).ToString.Equals(r(0).ToString))(0)(2).ToString)

Cheers

Hey, Thans for quick response. For unmatched data, it is filtering in different sheet, but it also need to be deleted from original sheet. How do we do that?

@ayushi_jain3

Instead of deleting you can reqrite the unmatched records

If you want to delete only then instead of linq use for each row in excel and delete row activity with if condition to check for match

Cheers

error in invoke code activity: “dt2 not declared. It may be inaccessible due to it’s protection level.”

@ayushi_jain3

Did you add both the datatables as arguments and send them in?

Cheers

Yes, I read both the sheets using write range workbook activities inside dt1 and dt2 variable and then copied the code above in invoke method activity for matched data as unmatched one is coming out as expected

Hi,

Try this

unmatchedRows = (From row In dtSheet1.AsEnumerable()
Where Not dtSheet2.AsEnumerable().Any(Function(r) r.Field(Of String)(“ColumnA”) = row.Field(Of String)(“ColumnA”))
Select row).CopyToDataTable()

matchedRows = (From row In dtSheet1.AsEnumerable()
Where dtSheet2.AsEnumerable().Any(Function(r) r.Field(Of String)(“ColumnA”) = row.Field(Of String)(“ColumnA”))
Select row).CopyToDataTable()

@ayushi_jain3

In the invoke code edit arguments are you passing these?

Cheers

Passed the arguments, but what value do I need to provide in “Value” Field?

@ayushi_jain3

You need to pass your datatable variables…basically it a code mudule or a separate workflow think of invoke code like that…how do you link variables from
One workflow to arguments in another the same way you would link or pass here

Cheers



Getting an error…PFA

Hi @neha.upase , after this method…how do I copy value of column 2&3 from dt2 to column 2&3 of dt1?