Excel Automation - Check and Identify duplicate records/rows

Help Studio
1

Hello Everyone

Happy Sunday!

I have duplicate records/rows in my excel and I wanted to know or have an “identifier” for all the records that are duplicate. What I was thinking was to perform each row check but I don’t know how to code it and I don’t know if that’s the best approach. Anyone can share light on this please? Please see below for more information. Thanks

Raw data (Filter: IF First Name, Last Name, Company, and Location have the same values)

ID First Name Last Name Age Company Location
1 James Fernandez 20 Company A US
2 James Fernandez 20 Company A US
3 James Fernandez 21 Company B US
4 Toshiro Shibuya 21 Company A Asia
5 Escanor Lion 22 Company A UK

“Expected” output

ID First Name Last Name Age Company Location Comment
1 James Fernandez 20 Company A US Duplicate records
2 James Fernandez 20 Company A US Duplicate records
3 James Fernandez 21 Company B US Single record
4 Toshiro Shibuya 21 Company A Asia Single record
5 Escanor Lion 22 Company A UK Single record

As you can see, I added new column so that when I use read range after doing this, the Bot will know if the records have duplicate and will be treated sa duplicate or not because the process will be different if the records have duplicate records or not.

3 Likes
2

Hi @Callos_James_AU u can use read range which will give u datatable.Add Data column Comment.Then use for each row and store each row’s value in variable and use that variable to compare in if conditons with second row if complete row matches then u can assign row(“Comment”)=Duplicate records.After the loop use write Range.

3

Hello @Tushar_Karkera!

Thanks for these! However, I cannot apply it in code. If you’re free, would you be able to provide sample in codes? Thanks and sorry for bothering you on Sunday.

4

Hello @Tushar_Karkera how are you? by any chance, can you give me a sample of what you have suggested? Thanks

5

Hi ,

Use
“Remove Duplicate Rows” activity in UiPath

6

Check this workflow
Duplicate.zip (9.5 KB)

I’m comparing only two columns you can change according to your condition.

Thanks
@Callos_James_AU

3 Likes
7

Hello @hasib08 This solves my problem. Thank you very much! I appreciate your help!! :slight_smile:

8

Hello @hasib08 Just a question, what’s the purpose of dt.clone? why do you need to use it on this scenario? Thanks

1 Like
9

Clone creates a new DataTable with the same structure as the original DataTable.
By cloning we can compare rows and will update the column accordingly.

Thanks
@Callos_James_AU

10

ohhhh! I didn’t know about any of this. Thanks for your help and explanation! Appreciate it much! :slight_smile: @hasib08

1 Like
11

Hello @hasib08 How are you? I need to modify the output of the bot but I cannot get the expected output. I need to put identifier/classifier for all the duplicate columns, for example…

ID First Name Last Name Age Company Location Comment
1 James Fernandez 20 Company A US Duplicate Entry 1
2 James Fernandez 21 Company A US Duplicate Entry 1
3 James Fernandez 20 Company b US Duplicate Entry 2
4 James Fernandez 21 Company b US Duplicate Entry 2
5 Escanor Lion 22 Company A UK Singe Record

I tried putting counter but it is giving me this output :frowning: Appreciate your help again!

ID First Name Last Name Age Company Location Comment
1 James Fernandez 20 Company A US Duplicate Entry 1
2 James Fernandez 21 Company A US Duplicate Entry 2
3 James Fernandez 20 Company b US Duplicate Entry 3
4 James Fernandez 21 Company b US Duplicate Entry 4
5 Escanor Lion 22 Company A UK Singe Record
12

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