Get Duplicates in One Column in Excel

Hello Everyone

I have a scenario that I need to check if there are duplicate records in one column then put comment in other column. Would anyone here please help me? Thanks everyone!!

Data

Name | Comment
James
James
James
Paul
Ryan

Output

Name | Comment
James | duplicate
James | duplicate
James | duplicate
Paul
Ryan

You can read data to a datatable (Read Range activity)
Then use 2 Loop each Row in datatable to count data to check if it’s duplicated

Hello @Doanh would you be able to show how to perform loop each row? Thanks

@James_Callos
Excel Application Scope
__Read Range -> save data to an datatable variable (eg. dtData)
__For each row in dtData
____intIndex = dtData.Rows.IndexOf(row)
____For each row2 in dtData
______intIndex2 = dtData.Rows.IndexOf(row2)
______If row(“Name”) = rows(“Name”) and intIndex <> intIndex2
________row(“Comment”) = “duplicate”
________Break;
__Write Range -> write dtData back to Excel

1 Like

Hello @Doanh thanks for this but it’s not working or maybe im doing it incorrectly? is the intindex type is int32? Thanks

Hello @Doanh

Good day!

Just to give you a gist of my workflow…

Read range (dt1)

For each row (dt1)

Workflow
in_row = row

this is where I need to check the duplicate then pass the status to out_Remarks if duplicates…

@James_Callos
Please be noticed that you have to use a difference variable name for the 2nd For each row (i’m using row2)
____For each row2 in dtData

I also found that you’re using in_Row variable
-> it should be
row(“IMP_Item”).tostring() = row2(“IMP_Item”).tostring() and index <> index2

you also should add a Break activity for more performance
The intIndex type is int32

Hello @Doanh

I followed all your instructions but still the same :frowning:

image

Break activity should be in same block with
Assign out_BotStatus = “please work”
It will run when the condition is TRUE

Hello @Doanh

Sorry, it’s working but it’s populating the unexpected. Could you please check my workflow? Thank you!CheckDuplicates.xaml|attachment (11.4 KB)

@James_Callos
Please attach your xaml fiile

Hey @James_Callos

Please refer this ready-made xaml file, you can right away run this, i think it will satisfy your requirement.

CheckingDuplicate.zip (1.1 MB)

Thanks
Goutham Vijay

1 Like

Hello @GouthamVijay Thanks for this! It really helps, however, this is the real data that i’m expecting. Would you please help again? Sorry for the confusion. So Item ‘111’ should be the only item to be check if it has multiple items if the Name - Age - Company are the same (group). Thank you

Name Age Company Item Comment
James 25 Company A 111 Exception. Multiple Items
James 25 Company A 123 Exception. Multiple Items
James 25 Company B 1 Okay
Roma 25 Company B 143 Okay
Roma 25 Company B 143 Okay
James 25 Company A 222 Okay
James 25 Company A 221 Okay

@James_Callos
Please check my codeCheckDuplicate.zip (1.1 MB)

If i’m not wrong with the understanding, Item 111 should be the only item needed to check for duplicates, why not for other item do you have any restrictions?
If that so then you can apply the following logic,
in the place of

row(“Name”).ToString=val(“Name”).ToString
you can replace to
(row(“Name”).ToString=val(“Name”).ToString) AND row(“Name”).ToString=“111” AND val(“Name”).ToString=“111”

Hello @GouthamVijay apologies for the confusion. What I was saying is if the records have duplicates filtered by Name, Age, and Company then the Bot will check if the item contains item 111, if it does, check if it has other item account then comment exception multiple item codes. If it is only item 111, comment multiple 111 item codes. :slight_smile: Sorry for the confusion again

Sorry @James_Callos i couldn’t able to understand, you can try modifying some conditions inside my code.

Hello @GouthamVijay apologies on the late reply. I was sick. I checked your codes and it is checking if it found duplicate, my concern is for example…

so the condition or logic should be if Name, Age, and Surname are the same and has duplicates, it will check the Item column if it only has Item 111 or if it has multiple item 111, if yes, comment duplicate, if not, okay or proceed.

Name Age Surname Item Comment
James 12 Callos 111 duplicate
James 12 Callos 123 duplicate
Joy 13 Joy 111 duplicate
Joy 13 Joy 111 duplicate
Escanor 14 Merlin 123 ok
Rom 13 Kapoor 222 ok
Rom 13 Kapoor 222 ok

Sorry for the late response,
ok i understood, we should ignore other than 111 item for commenting Duplicate. Then how item 123 has been commented duplicate in the above example.