How to find duplicate values in .NET Excel Library

14 Aug 20263 minutes to read

The following code illustrates how to find the duplicate values in the Excel document using formulas.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    //Loads an existing file.
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Find duplicate values in the column
    for(int i = 2; i <= worksheet.UsedRange.LastRow; i++)
    {
        worksheet.Range["D" + i].Formula = $"=IF(MATCH(C{i},C$2:C{i},0)=ROW(C{i})-1,1,0)";
    }

    //Saving the workbook 
    workbook.SaveAs("Output.xlsx");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    //Loads an existing file.
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Find duplicate values in the column
    for(int i = 2; i <= worksheet.UsedRange.LastRow; i++)
    {
        worksheet.Range["D" + i].Formula = $"=IF(MATCH(C{i},C$2:C{i},0)=ROW(C{i})-1,1,0)";
    }

    // Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx

    'Loads an existing file.
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Find duplicate values in the column
    For i As Integer = 2 To worksheet.UsedRange.LastRow
        worksheet.Range("D" & i).Formula = $"=IF(MATCH(C{i},C$2:C{i},0)=ROW(C{i})-1,1,0)"
    Next i

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using