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

[Anil_G (Anil Gorthi)]

@kambhampati_manisridhar

Did you try the linq query above…can you share the input files please…even with dummy data would be fine…as it is working on my side

Cheers

[kambhampati_manisridhar]

prev month.xlsx (12.6 KB)
currentmonth.xlsx (10.7 KB)
Hi please note that these are just sample files there is no column postion fixed and there are many other column names and other sheets as well from which i need to compare current months excel file in that with sheet name ineligibility notices with prev months ineligibility notices using columns case name and account number and only uncommon data need to be filtered same for the upcoming settlements sheet as well it would be really helpful if you share me with workflow or so thankyou

[Anil_G (Anil Gorthi)]

@kambhampati_manisridhar

Joined data(OnlyNewwell)

After filtering for only prev month

Process followed

Hope this helps

cheers

[Nitya1 (Nitya Tomar)]

Hi @kambhampati_manisridhar

Try this-

1. Use the “Read Range” activity to read the data from both Excel files into separate DataTable variables. Let’s call them dtFile1 and dtFile2.
2. Use the LINQ query to filter the data based on sheet name. Assuming the column names are “Case Name” and “Account Number” and the desired sheets are “Upcoming Settlements” and “Ineligible Notices”:

Dim filteredFile1 = dtFile1.AsEnumerable().Where(Function(row) row.Field(Of String)(“SheetName”) = “Upcoming Settlements”).CopyToDataTable()
Dim filteredFile2 = dtFile2.AsEnumerable().Where(Function(row) row.Field(Of String)(“SheetName”) = “Ineligible Notices”).CopyToDataTable()

3. Compare and filter the unmatched data: Use LINQ to compare the selected columns and filter the unmatched data:

Dim unmatchedData = filteredFile1.AsEnumerable().Except(filteredFile2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

4. Write the unmatched data to a new Excel file: Use the “Write Range” activity to write the unmatchedData DataTable to a new Excel file.

Thanks!!

[kambhampati_manisridhar]

Hi thankyou but i need to compare 2 excel sheets like current months excel with previous month excel in that current month’s upcoming settlements with previous months upcoming settlements and current month’s ineligiblity notices with previous months ineligibility notices using columns case name and account numbers as columns

[Anil_G (Anil Gorthi)]

@kambhampati_manisridhar

Comparision can be on anythinn…but the final concept would remain same…if you follow the same steps with sheets you need you would get the output

Cheers

[kambhampati_manisridhar]

Hi thankyou it is working can you explain the logic behind this

[Anil_G (Anil Gorthi)]

@kambhampati_manisridhar

Joining both the table…with full join…when you do a full join matched record will be matched and unmatched records will be added but with empty values for remaining columns

So after join if we filter dt1 related columns with empty we will get the records which are not matched in dt2 and viceversa

Cheers