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
- First join dt1 and dt2 on the required columns and select outer join
- 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
- 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
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