Excel - Marking Duplicates in another column

Hi, I have an excel of say 2 column (Employee id and client ID).

Like the below
image

I need to highlight the duplicates (where Employee_ID and Client_ID) is duplicated, and write the value as “Duplicate” in the third new column named “Status”. Challenging part is, i need to mark only the duplicate values (should not mark the originals as duplicate).
Like below :
image

Appreciate any help on this. Tried various methods, but not getting the expected results.
Note : this is part of a big process, so any simple flow would be appreciated.

1 Like
  1. Sort green cols
  2. for each
    2-1. read row and save contents of a line
    2-2. read next row
    2-3. if 2-2row matches 2-1row, mark orange column of 2-2row

this is more verbose than writing .NET code or sql, but is easier to understand

1 Like

Read excel and save in a Datatable (say ID_DT)

For Each loop on ID_DT - loop 1
=====emp_ID = row.item(“EMPLOYEE_ID”).ToString
=====For Each loop on ID_DT - loop 2
==========if row.item(“CLIENT_ID”) Contains (emp_ID)
===============row.item(“Status”) = “DUPLICATE FOUND”
==========else
===============
=====END loop 1
END loop 2

Write Datatable to Excel

1 Like

Hi,

Hope the following sample helps you.

Sample20200129-1.zip (19.2 KB)

Regards,

1 Like

Thank you so much @Yoichi, That was quick!
It works as I expected. Thanks a lot! :slightly_smiling_face:

1 Like

Thanks @sumitd for your time! will keep this method in mind as well.

1 Like

Thanks @Sumire for your quick response.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.