How to Find Duplicate Rows with Multiple Columns in Excel Sheet?

Help Technology Integrations
1

Hello!
I have an Excel files of important documents, and i need to find duplicate rows with multiple columns in that spreadsheet. Is there any reliable solution which can help me with cleaning my Excel Data?

2

Hello @mishti1,

Use LINQ in an Assign activity:

uniqueDT = dt.AsEnumerable().GroupBy(Function(r) r("ColumnA").ToString & "|" & r("ColumnB").ToString).Select(Function(g) g.First()).CopyToDataTable()

Read Range → Assign (above) → Write Range. Works well, no need for Invoke Code since it’s a single line.

3

Hi @mishti1

you can find duplicate rows using LINQ in UiPath. Here is a simple way:

Step 1: Use Use Excel File and read your Excel data using Read Range.

Step 2: Store the output in a DataTable, for example dtData.

Step 3: Add an Assign activity.

Step 4: Create another DataTable variable called dtDuplicates.

Step 5: Use LINQ to group the rows based on the columns you want to check. For example, if you want to check Name + Email + City:



dtDuplicates = dtData.AsEnumerable().
GroupBy(Function(row) New With {
    Key .Name = row("Name").ToString,
    Key .Email = row("Email").ToString,
    Key .City = row("City").ToString
}).
Where(Function(g) g.Count() > 1).
SelectMany(Function(g) g).
CopyToDataTable()

Step 6: Finally, use Write Range to write dtDuplicates to a new Excel sheet.

This will give you the rows where Name + Email + City are repeated together.

If you have different columns, just replace Name, Email, and City with your column names.

4

Hi @mishti1 adding to what Mayuresh posted…

that CopyToDataTable will throw if there are no duplicates at all, the error is something like the source contains no DataRows. So its worth checking first with something like

if dtData.AsEnumerable().GroupBy(…).Where(Function(g) g.Count() > 1).Any() then copy, else just leave dtDuplicates empty

the other thing I would watch is trailing spaces and casing. Excel data usually has both, so “John” and "john " end up in different groups and you miss real duplicates. Adding .Trim.ToLower on each key usually fixes it, like Key .Name = row(“Name”).ToString.Trim.ToLower

just keep in mind that changes only how they are compared, the rows you get back are still the original ones

5

@mishti1

Add two assign activities in studio, Create one variable lstDataTable and variable output should be List
Pass this in First assign activity in To section and in Value section enter the below expression,
dtInput.AsEnumerable().
GroupBy(Function(r) New With {
Key .PA = r(“PA Number”).ToString.Trim,
Key .Contract = r(“Contract ID”).ToString.Trim,
Key .Supplier = r(“Supplier Name”).ToString.Trim,
Key .Facility = r(“Facility Name”).ToString.Trim
}).
Where(Function(g) g.Count() > 1)
.SelectMany(Function(g) g).
ToList()

And in second assign activity, create a variable called dtOutput variable type should be DataTable,
Pass this in To section of second assign activity and in value section pass the below expression,
If(lstDataTable.Count()>0, lstDataTable.CopyToDataTable(), dtInput.Clone())

In next operation you can use that datatable, and this wont through any exception even there are no duplicates, it will create empty datatable with your input column names.

If you find this helpful, please mark it as solution.
Happy Automation

6

@mishti1 Yes, you can handle this reliably in UiPath. If the duplicate needs to be identified based on multiple columns, you can follow this approach:

  1. Use Use Excel File and Read Range to read the Excel data into a DataTable, for example dtInput.

  2. Decide which columns together should define a duplicate.
    For example, if a row is considered duplicate when Document Name, Document Number, and Date are the same, use these three columns for comparison.

  3. Use an Assign activity with LINQ to group the rows based on those columns.

    Example:

    dtInput.AsEnumerable().GroupBy(Function(r) New With {Key .DocName = r("Document Name").ToString.Trim, Key .DocNo = r("Document Number").ToString.Trim, Key .DocDate = r("Date").ToString.Trim}).Where(Function(g) g.Count > 1)

    This will identify the groups that contain duplicate records.

  4. If your requirement is only to remove completely identical rows, you can also use the Remove Duplicate Rows activity. However, for duplicates based only on selected columns, LINQ gives you better control.

  5. Once the duplicate rows are identified, you can either:

    • remove the additional duplicate rows and keep one record,

    • move the duplicates to another DataTable/Excel sheet for review, or

    • add a new column such as Duplicate = Yes so the user can verify them before deletion.

  6. Finally, use Write Range to write the cleaned DataTable back to Excel.

I would recommend identifying and marking the duplicates first instead of deleting them directly. Once the results are validated, you can remove the duplicate records. This is safer when working with important document data.

8

Hi @mishti1

Yes, the approach mentioned earlier should work for identifying duplicate rows based on multiple columns. You can compare the required columns together and then use Group By or LINQ to identify records that occur more than once.

Hope this helps!