Compare two separate excel file data row by row

Help
1

Hi all, I have two excel file which contain data now i have to compare data between these two excel file row by row e.g first row in first file with first row in second file. and if there is any mismatch cell value found in any row then highlight that complete row in second sheet. here is the file.Help…
Book1.xlsx (8.6 KB)
Book2.xlsx (8.6 KB)

2

Hi Sandeep.

I’d probably do the following:

  • Read each excel file into a unique Data Table (DT1 / DT2)

  • Add a new column onto the end of DT2 called “Mismatch” or something similar

  • For each row in DT1, loop around iterating through the columns checking that DT1 (row/column) = DT2 (row/column)

  • Use an if statement so that if DT1 (row/column) isn’t equal to DT2 (row/column) add a value of “true” to DT2(row/“Mismatch”)

  • Once the process has finished, you can filter DT2 by any “true” values and export it to excel.

  • It would probably be easier if you had conditional formatting on the new sheet to highlight the row if the value of Mismatch was “True”

I’ve only given a quick overview of the steps, let me know if you need any specific help

1 Like
3

Once you read both the excel files and stored the data in datatable, execute a linq query to check the is there any mismatch. If there is mismatch return the second data table row. The result of the linq query will be Array of datarows from second table.
Loop through each record in the array, find the row index in the second table for each array row and highlight that row.