Get Duplicates in One Column in Excel

Learn Academy Feedback
, ,
1

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

2

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

3

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

4

@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
5

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

image
image1187×1286 40.3 KB

6

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…

7

@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

8

Hello @Doanh

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

image
image1141×1357 42.7 KB

image

9

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

10

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)

image
image1426×1118 42.8 KB

11

@James_Callos
Please attach your xaml fiile

12

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

3 Likes
13

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
14

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

15

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”

16

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

17

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

18

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
19

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.

20

Hi you could use this code for easier reference
– Use a for each row in data table activity
– Inside that use an assign activity to get current row value from “Name”
eg. Name = Row(“Name”).ToString
– Then use an if else activity to check if there are duplicates within the column
You might want to use this LINQ function:
(From x In (dtYourTable.defaultview.totable(False, “Name”)).asenumerable() Where x(“Name”).Equals(Name) Select x).ToArray.Length > 1
– Inside of that if activity put an activity for writing comment in the next cell

Note: use counter to know if what cell should you write the value for the comment

Here is the visual example of the code:

image
image631×681 18.6 KB