topic RE: Remove Duplicates from excel in Product Forum

Remove Duplicates from excel

NupurSood — Wed, 02 Dec 2020 15:00:00 GMT

Hello All

Am looking a a code that is able to remove all duplicates from excel directly. I do not want to take the data in a collection and then remove duplicates because of huge volume. The de-duping must happen on the basis of all columns of excel and not just one defined column.

Thanks in advance for your guidance

Regards
Nupur

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

ewilson — Wed, 02 Dec 2020 18:05:00 GMT

Are you writing your own code, trying to automate Excel via the UI, or working with one of the Excel VBOs on the DX? There is a deduplication method on the Range object in VBA. You can find more about it here.

Unfortunately, this feature isn't included in the Excel VBOs provided by BP at the moment, but with the Microsoft documentation referenced above you should be able to add it to your local VBO.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Remove Duplicates from excel

NupurSood — Wed, 02 Dec 2020 18:08:00 GMT

Am trying to utilize one of the excel VBOs itself and add a page to it that removes dupes by taking into consideration all the columns of excel.
My challenge is converting "Columns:=Array(1,2)" to the format that VB would accept. I keep running into type mismatch error.

Thanks
Nupur

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

ewilson — Wed, 02 Dec 2020 18:30:00 GMT

Can you share a screenshot of your code or copy-and-paste it in the thread?

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Remove Duplicates from excel

zdenek.kabatek — Thu, 03 Dec 2020 15:29:00 GMT

Hello, Nupur,

how are you :)?

If you need to consider all columns and you are familiar with C# (may be there is an alternative in VBA) then this one line code does what you need 😉

Output_Collection = Input_Collection.DefaultView.ToTable( /*distinct*/ true);

The input parameter to the action is the collection - you get it from Excel via Get Worksheet (I know you know it) - and output collection is only unique rows.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Remove Duplicates from excel

NupurSood — Thu, 03 Dec 2020 17:12:00 GMT

Hello Zdenek

Very well, hoping the same for you 🙂 And thank you for coming to rescue.
Am afraid that the collection approach does not work for me since the amount of data in my excel is huge (~50,000 rows) and getting data into collection freezes up my BP. Am trying to achieve the same directly in excel instead.

Regards

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

ewilson — Thu, 03 Dec 2020 17:24:00 GMT

@NupurSood another way you can achieve this since you're digital worker will need a running instance of Excel is to simply attach to that instance and perform the deduplication via the Excel UI which is detailed in the following article:

https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Remove Duplicates from excel

NupurSood — Thu, 03 Dec 2020 17:25:00 GMT

Thank You for your response. Below is the code am using for reference and the challenge is to pass array of column numbers instead of just one column in the highlighted portion

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

NupurSood — Thu, 03 Dec 2020 17:29:00 GMT

Thank You

I can adopt this approach but I was hoping to do something directly via code stage to be able to have more optimal/robust solution. If I am unable to crack the code, this would be helpful.
Thanks again for all your guidance.

Regards

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

NileshJadhavBP — Thu, 03 Dec 2020 17:41:00 GMT

Hey Nupur,

You can achieve this using Code stage and have to write custom code along adding related dll and namespace.

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
ADP
India
------------------------------

RE: Remove Duplicates from excel

ewilson — Thu, 03 Dec 2020 18:10:00 GMT

@NupurSood I think part of the problem is your "Columns" entry. The method expects a Variant array of column indexes. However, Variant is no longer a supported type, so I think you can create an array of Object type (which will just be the column indices). Here's an example:

Dim varData(2) As Object
varData(0) = "1"
varData(1) = "2"

ws.Range("A1:C100").RemoveDuplicates(Columns:=varData, Header:=2)

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Remove Duplicates from excel

sonuiiml — Thu, 03 Dec 2020 21:43:00 GMT

Hi Nupur,
You can use the below code

Dim wb,ws, dup as object

try

wb= getworkbook(handle,workbook)
ws= getworksheet(handle,workbook, worksheet)

dup= ws.application.range(Input_Range).removeduplicates(columns:=1)

success= True

Catch e as exception
success = False
Message = e.message
Finally
wb=nothing
ws=nothing
dup=nothing
end try

You have to supply inputs for

1. Workbook as text
2. Worksheet as text
3. input_Range as text Eg. for full column it will be "B:B" for anything else "A1:B8"

4. handle as number

be mindful of the column number you are entering in the line

dup= ws.application.range(Input_Range).removeduplicates(columns:=1)

'here in this example its column A

1 refers to column A
2 refers to columns B

Good luck

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: Remove Duplicates from excel

NupurSood — Fri, 04 Dec 2020 12:20:00 GMT

Thanks Eric

I get the below shown error with the Array defined as suggested.

Regards

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

ritansh.jatwani — Fri, 04 Dec 2020 15:45:00 GMT

Hello @NupurSood,

Do you want to remove the duplicates based on one specific column value or you wanted to compare the full rows and then remove the data?

Since the code stage will vary based on the requirement. Please confirm and i can share the code with you. :)

------------------------------
Ritansh Jatwani
Consultant
EY
Gurgaon
------------------------------

RE: Remove Duplicates from excel

NupurSood — Fri, 04 Dec 2020 16:33:00 GMT

Hello @Ritansh Jatwani

I am looking at comparing full row please

Thanks
Nupur

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Remove Duplicates from excel

ewilson — Fri, 04 Dec 2020 17:13:00 GMT

@NupurSood It seems to be a rather weird conflict between VBA and VB.NET. It doesn't help that the documentation for the Excel interop library is vague as well. What I've found is similar to what Susamay posted below. It seems if you pass in a single column index things work fine. Alternatively, you can pass in an array of indices if you know them ahead of time. Here are examples of both approaches that I've verified on my machine:

ws.Range(myRange).RemoveDuplicates(Columns:=New Object() {1, 2}, Header:=1)

or

ws.Range(myRange).RemoveDuplicates(1,1)

The first example shows how you could pass in an array of indices. I've tried pulling that array creation out and assigning it to an actual Object variable, but the same data type exception is thrown. Very weird. The second example shows passing in a single column index.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Remove Duplicates from excel

ArchanaR — Fri, 13 Aug 2021 14:50:00 GMT

Hi Nupur,

Were you able to get a custom code that works for this scenario?

------------------------------
Archana R
Consultant
Deloitte
Europe/London
------------------------------

RE: Remove Duplicates from excel

michaeloneil — Fri, 13 Aug 2021 15:09:00 GMT

Hi

I previously created a vbo to do this but it does work on a named worksheet and range so you would need to do it for each worksheet but the range you can set to whatever you need to be. See below:

Dim wb, ws As Object
Dim excel, sheet, range As Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

'Range("B2178:F2187").Select
ws.Range(Reference).RemoveDuplicates (Columns:=Column_number, Header:= 2)

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Remove Duplicates from excel

rwesbokas — Sun, 15 Aug 2021 22:40:00 GMT

Remove duplicate values

Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.
Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates. ...
Click OK.

Follow these steps:

Select the range of cells, or ensure that the active cell is in a table.
On the Data tab, click Remove Duplicates (in the Data Tools group).
Do one or more of the following: television wall mount at wallmountedhub...
Click OK, and a message will appear to indicate how many duplicate values were removed, or how many unique values remain.

If you want to remove duplicate data from a list you can use Remove Duplicates function in excel directly, then the unique data will be saved in the list, the duplicate one will be removed. But if you want to remove both duplicates from your list, this function cannot help you.

------------------------------
rwe sbokas
------------------------------

RE: Remove Duplicates from excel

zdenek.kabatek — Mon, 16 Aug 2021 06:32:00 GMT

Hi, Nupur,

if you want one row code solution I can offer you in C# (you can change the language in VBO in Code Option I guess you know where)

Output_Collection = Input_Collection.DefaultView.ToTable( /*distinct*/ true);

Put above code in code stage. Input parameter is Input Collection and output parameter is Output_Collection. That returns only rows which are unique and compares all the columns.

Regards,

Zdenek

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------