Find duplicates comparing two excel files and add remarks

[uio (uipath test)]

Hi All,

I have to compare two Excel files MasterFile & InputFile to find duplicates in a column “Full Name”.

If a duplicate value found, add remark in the InputFile as “Duplicate found” and “Not a duplicate” in the remaining unique value rows.

Thank you,
Uio.

[mkankatala (Mahesh Kankatala)]

Hi @uio

Could you share your input files, If it doesn’t have any confidential information.

[uio (uipath test)]

No problem sharing it. Just random values.

InputFile.xlsx (9.2 KB)
MasterFile.xlsx (9.2 KB)

[mkankatala (Mahesh Kankatala)]

Okay @uio

→ Use the Read range workbook activity to read the InputFile.xlsx and store the output in a DT1 variable.
→ Use another Read range workbook activity to read the MasterFile.xlsx and store the output in DT2 variable.
→ Use the assign activity and keep the below LINQ Expression,

- Assign -> DT_Output = DT1.AsEnumerable.Select(Function(r) DT1.Clone.LoadDataRow({r(0),r(1),r(2),if(DT2.AsEnumerable.Any(Function(r2) r("NAME").ToString=r2("NAME").ToString),"Duplicate found","Not a duplicate")},False)).CopyToDataTable()

→ Use the write range workbook activity to write the DT_Output in to InputFile.xlsx and to same sheet.

Check the below workflow for better understanding,

Check the below output file,
InputFile.xlsx (9.1 KB)

Hope it helps!!

[uio (uipath test)]

Can you provide the project file please ?

[mkankatala (Mahesh Kankatala)]

Sure @uio

Check out the below workflow, change the path of the excel files in the activities.
Regex_Practice.xaml (19.9 KB)

If you find the solution for your query, Make my post Mark as solution to close the loop.

Happy Automation!!

[uio (uipath test)]

How do I do it using Write cell activity ?

[mkankatala (Mahesh Kankatala)]

write cell activity used to write the data in the single excel… @uio

Could you elaborate your query. what your need

[uio (uipath test)]

I just need to lookup for duplicates and if a duplicate is found, will add a remark and process the next row item. And if the next row isn’t a duplicate, I have to just process it.

[mkankatala (Mahesh Kankatala)]

Is the output that I have given is not reached your expected output… @uio

If not then show me an example then we will provide the solution for your query.

[uio (uipath test)]

I was exploring methods and found this method will suit for my flow.

ComparingTwoExcelsToFindDuplicates.zip (4.5 KB)

Thank you for your support.

Since the method you gave works for my initial requirement, marking it as a solution.