Compare 2 sheets using 2 column names and get the uncommon data from 2 files

Hi all i have a situation where i need to compare 2 excel files in that out of multiple sheets i only need to compare 2 sheets naming upcoming settlements and ineligible notices using specific 2 column names naming case name and account number and i need to filter the unmatched data from both the sheets to new excel sheet please help me with this if any linq code is provided will be great

@kambhampati_manisridhar you have two excel files.
every excel files conatains multiple sheets and you want to compare two on the base of common column. Right?

Yes you got my point

And need to get uncommon data from both the files

@kambhampati_manisridhar Can you give me sample files?

Book.xlsx (11.1 KB)
similarly 2nd file there is much more data in it and files keep changing and their column name is same but column position keeps changing

And also there are many more sheets in it it is just a sample file

@kambhampati_manisridhar no problem we can apply checks to get exact sheet. give me few moments

Ok can you please further help me how to overcome it


You can perform an outer join on both the excel sheets after reading into datatable

Then filter with empty case name and account number to get the unmatched rows

If linq only is needed then check this

Dt1.AsEnumerable.Where(function(x) dt2.AsEnumerable.Where(function(y) y("CaseName").ToString.Equals(x("CaseName").ToString AndAlso y("Account Number").ToString.Equals(x("AccountNumber").ToString).Count=0).CopyToDataTable

This will give unmatched from dt1 …interchange both to get the unmatches from dt2

Also consider checking .Count>0 before copydatatable to check if there are any matched rows at all

Hope this helps


Hi you mentioned about filtering the columns with empty but does that work

Hey could you tell me step wise solution which i need to follow


  1. First join dt1 and dt2 on the required columns and select outer join
  2. For non matching columns from dt1 …filter the dt2 related columns in the joined datatable which generally if the name is same would be Accountnumber1 with empty values…this column would be empty when only dt1 has data and not dt2
  3. For getting non matched on dt2 …filter dt1 related joined columns for empty…which will be the non matched from dt2


Hi i tried that but no output is coming


Did you try the linq query provided?

And can you show how you tried?

Can you please write the output of join into excel and then check if all rows are matchign or yhere are any unmatched rows at all


Hi these are the images of sequences that i had followed


Please use equals instead of not equals in the join activity


Hey have tried that also output is not coming


Input datatable for filter should be the output of join which is dt5 as per your flow


Thank you but that is not working as well is there any other way or method