topic RE: How to remove duplicate value in collection in Product Forum

How to remove duplicate value in collection

Rinesh — Thu, 15 Jul 2021 11:20:00 GMT

Hi there,

Hope someone can assist with the below please.

I have a collection which contains 2 columns of data. [ID Number] and [Surname]. I would like to remove duplicate ID numbers from this collection and place them into another collection. So i will be left with only unique ID numbers in my original collections.

Does anyone know how I could do this without the use of a code stage.

Thank you

------------------------------
Rinesh Singh
------------------------------

RE: How to remove duplicate value in collection

EmersonF — Thu, 15 Jul 2021 12:01:00 GMT

@Rinesh Singh,you can be using the action remove duplicates from the collection manipulation, if you don't have this vbo follow the asset link in our DX
Blue Prism Digital Exchange

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
America/Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

RE: How to remove duplicate value in collection

Rinesh — Thu, 15 Jul 2021 12:48:00 GMT

Hi Emerson,

Thank you for your help!

I have had a look at the package on the link you provided but it does not make mention of "remove duplicates", The guide for vbo outlines the below as included in this asset.

Options included in the utility:
• Append Field (Number)
• Append Field (Text)
• Append Rows to Collection
• Collection Contains Field
• Collection Contains Value
• Copy Rows
• Delete Column
• Delete Field
• Field Exists
• Fill Blanks
• Filter Collection
• Get Collection Fields
• Merge Collection
• Read Collection Field
• Remove dots from headers
• Remove Empty Rows
• Remove Null Rows
• Rename Collection Fields
• Rename Field
• Reverse Collection
• Set Collection Field
• Set Column Names From Expected Collection
• Set Column Names From First Row
• Sort Collection
• Split Collection
• Transpose Collection

------------------------------
Rinesh Singh
------------------------------

RE: How to remove duplicate value in collection

EmersonF — Thu, 15 Jul 2021 13:24:00 GMT

Try adding a new action to VBO
Write a code stage with this code

Dim pNewDataTable As DataTable
Dim pCurrentRowCopy As DataRow
Dim pColumnList As New List(Of String)
Dim pColumn As DataColumn


For Each pColumn In Input_Col.Columns
	pColumnList.Add(pColumn.ColumnName)
Next

pNewDataTable = Input_Col.DefaultView.ToTable(True, pColumnList.ToArray)

Out_Col = Input_Col.Clone

For Each pCurrentRowCopy In pNewDataTable.Rows
	Out_Col.ImportRow(pCurrentRowCopy)
Next

And make the following outputs and inputs

Input:

Output:

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
America/Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

RE: How to remove duplicate value in collection

Rinesh — Thu, 15 Jul 2021 19:30:00 GMT

thanks once again Emerson but unfortunately using code is not an option in our business. Was really hoping for a no code solution.

------------------------------
Rinesh Singh
------------------------------

RE: How to remove duplicate value in collection

AvinashRamkumar — Fri, 16 Jul 2021 01:47:00 GMT

Hi Rinesh,

Using code stages/custom object is easier.
But if your business has restrictions, could try the below method.
1) Create an empty collection (ColB), assume existing collection is ColA
2) Do a nested loop starting with ColA, if row does not exist in ColB, add row to ColB.
3) You can choose whether to delete or retain the copied row in ColA.

Hope this helps.

Thanks,

Avinash

------------------------------
Avinash Ramkumar
------------------------------

RE: How to remove duplicate value in collection

Rinesh — Fri, 16 Jul 2021 06:50:00 GMT

Thanks Avinash, let me try this and will feedback here once I am done.

------------------------------
Rinesh Singh
------------------------------

RE: How to remove duplicate value in collection

JiriHusner — Fri, 16 Jul 2021 09:55:00 GMT

@EmersonF

i have an error on line 3 when pasting into code stage, How can i correct it error?

Thank you

------------------------------
Jiri Husner
------------------------------

RE: How to remove duplicate value in collection

EmersonF — Fri, 16 Jul 2021 13:40:00 GMT

Sorry, I just missed an important part, please do not initialize your object declare the following DLL and Namespaces

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
America/Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

RE: How to remove duplicate value in collection

DevendraMaganti — Thu, 16 Jun 2022 10:57:00 GMT

Hi Avinash,
iam getting whole CollA to CollB.

Thanks and Regards,
DevendraNath

------------------------------
Devendra Maganti
------------------------------

RE: How to remove duplicate value in collection

Neel1 — Thu, 16 Jun 2022 11:11:00 GMT

hello @DevendraMaganti - you are not doing in correct way. Data1 and data2 are two collections.
you need to take reference of column of that collection like Data1.x and check the value in other collection.

P.S - go with code approach. extend your vbo with code stage. That will save the iteration time of collection.

------------------------------
Neeraj Kumar
Technical Architect
------------------------------

RE: How to remove duplicate value in collection

AravindBabu — Thu, 16 Jun 2022 13:20:00 GMT

Hi Devandra,

Steps to be followed here is.
1. Take one empty collection assume it Coll B and assuming existing collection is A.
2. Now start loop for Coll A
3. Start Loop for Coll B
4. Compare both collection values , If Yes do nothing, If No copy the coll A value to Coll B
5.Loop Coll B Ends.
6. Loop Coll A Ends.

------------------------------
Aravind Babu
------------------------------

RE: How to remove duplicate value in collection

NafizZainal — Fri, 17 Mar 2023 09:05:00 GMT

Hi Aravind,

Is this the solution u suggested? I did it but it didn't get to the decision stage in the loop. I need to check duplicates for 2 out of 3 column. Means that the value in column 1 AND column 2 should not have any duplicate in other rows.

Thanks

------------------------------
Nafiz Zainal
------------------------------

RE: How to remove duplicate value in collection

plnarayana777 — Fri, 17 Mar 2023 09:39:00 GMT

Hi Nafiz

Please can you paste the syntax in decision stage

and one more thing is there any restriction in using code stage.

Regards

Lakshmi Narayana

------------------------------
Lakshmi Narayana
------------------------------

RE: How to remove duplicate value in collection

Mukeshh_k — Fri, 17 Mar 2023 11:00:00 GMT

Hi @NafizZainal

Kindly go with the code stage rather than looping the collections as it might take longer iteration time when the collections are fully loaded, as suggested above by @Neeraj Kumar and you can find the code by Emerson Ferreira in the above discussion.

Its a One liner code to be specific ( Collection_Out=Collection_In.DefaultView.ToTable(true) ) , follow this approach if you are facing any difficulties implementing above approaches:

Process :

Object : Add an Action in one of your existing collection manipulation extended objects or create a new object and check the required Dll and Namespaes in the Initialize page> code options:

Add an Action page and add input and output collection and write below code:

Collection_Out=Collection_In.DefaultView.ToTable(true)

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------

RE: How to remove duplicate value in collection

NafizZainal — Mon, 20 Mar 2023 01:44:00 GMT

Hi Mukesh,

Is this going to work when I need to check only 2 columns with same value out of multiple columns? Here I attached the screenshot:

You can see I need to check for duplicate only for column 1 and column 3. What I need to do now is to remove Row 4 from Coll1 and add it into Coll2. Is the code stage possible for this scenario?

------------------------------
Nafiz Zainal
------------------------------

RE: How to remove duplicate value in collection

Vikram.R — Thu, 28 Sep 2023 09:09:00 GMT

Hi Will this work if the data is duplicates one is in upper case and other in lower case

------------------------------
Vikram Ramesh
------------------------------