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

Help Activities
, , , ,
1

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

2

@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?

3

Yes you got my point

4

And need to get uncommon data from both the files

5

@kambhampati_manisridhar Can you give me sample files?

6

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

7

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

8

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

9

Ok can you please further help me how to overcome it

10

@kambhampati_manisridhar

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

Cheers

11

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

12

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

13

@kambhampati_manisridhar

  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

Cheers

14

Hi i tried that but no output is coming

15

@kambhampati_manisridhar

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

Cheers

16

20230527_113321
20230527_1133211920Ă—2560 239 KB

20230527_113332
20230527_1133323120Ă—4160 1020 KB

20230527_113346
20230527_1133463120Ă—4160 768 KB

20230527_113403
20230527_1134033120Ă—4160 855 KB

20230527_113410
20230527_1134103120Ă—4160 841 KB

Hi these are the images of sequences that i had followed

17

@kambhampati_manisridhar

Please use equals instead of not equals in the join activity

Cheers

18

Hey have tried that also output is not coming

19

@kambhampati_manisridhar

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

Cheers

20

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