Get non-matching records in 2 data tables not working

Help Studio
1

Hello, I have 2 data tables (one from read range in Excel and the other from reading a Sharepoint List).

I am trying to compare these columns:

SharePoint List data table (masterlistDT) column: Last_x0020_Day_x0020_of_x0020_Em
TO
Excel data table column (excelDT): Last Day of Employment

Both these columns are in DateTime format (excel and sharepoint).

What I need to do is to find rows in masterlistDT where the column Last_x0020_Day_x0020_of_x0020_Em (which has null data) is not equal to the column Last Day of Employment in excelDT (this has date data)

This is what I have used:

masterlistDT.AsEnumerable().Where(function(row) Not excelDT.AsEnumerable().Select(function(r) r(“Last Day of Employment”).toString).Any(function(x) x = row(“Last_x0020_Day_x0020_of_x0020_Em”).toString)).CopyToDataTable()

But it’s always giving me 0 rows matched, although I have at least 2 rows where it should match i.e. blank in masterlistDT’s column but has data in excelDT’s column.

Would appreciate any help. Am out of ideas. Thanks very much!

Both these columns are formatted as DateTime. However, there is only date information in the

2

dtMatched = (From rowMain In dtMain.AsEnumerable
Join rowCommon In dtFirst.AsEnumerable
On rowMain(0).toString.Trim Equals rowCommon(0).toString.Trim
Select rowCommon).CopyToDataTable

dtUnMatched = (From rowOne In dtFirst.AsEnumerable
Where Not dtMatched.Tolist.Any(Function (r) r(0).toString.Trim.Equals(rowOne(0).toString.trim))
Select rowOne).CopyToDataTable

Try this and let me know

3

Hello Divyanshu, Thanks for responding!

I tried your code and it works but i eventually did it this way (there are 2 columns to compare):

excelDT.AsEnumerable().Where(Function(row) Not masterlistDT.AsEnumerable().Where(function( r ) r(“Title”).ToString.Equals(row(“Employee ID”).ToString) and r(“LastDayOfEmployment”).ToString.Equals(row(“Last Day of Employment”).ToString)).Any).CopyToDataTable

Thanks for your help!

4

Great :grinning: