topic Re: Clear Duplicate Values from Excel sheet VB code stage in Product Forum

Clear Duplicate Values from Excel sheet VB code stage

BENDRES — Thu, 07 Nov 2024 12:42:49 GMT

Hi Everyone,

Please assist me in getting the VB code for Blue Prism to clear duplicate values from an Excel sheet.

For reference, kindly refer to the attached image.

Re: Clear Duplicate Values from Excel sheet VB code stage

Mohamad_Dakkouri — Thu, 07 Nov 2024 19:43:01 GMT

What i can suggest you its to get your excel file as a collection then you make the remove duplicate and then you save your excel file.

It will be easier.

Is it ok for you ? or its mendatory to have a custom code stage ?

Re: Clear Duplicate Values from Excel sheet VB code stage

BENDRES — Fri, 08 Nov 2024 07:26:56 GMT

Hi @Mohamad_Dakkouri

I appreciate your suggestion.

Since I have a lot of data in 25 columns, I don't want to take it into collection because it could become stuck or there might be a format change.

It will work better with coustom code because just need clear the cell value.

Re: Clear Duplicate Values from Excel sheet VB code stage

Mohamad_Dakkouri — Sun, 10 Nov 2024 09:00:37 GMT

Hi @BENDRES

Follow these steps :

Go to MS EXCEL VBO and add a new action, name it 'Apply Remove Duplicate [CUSTOM]'. Then drag and drop a code stage like this :

Now open the code stage and provide these inputs :

Do the same for output :

in code stage put this :

Dim ws as Object

Dim previousVAL As String

Dim currentVAL As String

ws = GetWorksheet(handle, workbookname, worksheetname)

Try

previousVAL = ""

For i = start_row To last_row

currentVAL = ws.Cells(i, remove_duplicate_column).Value

If currentVAL = previousVAL Then

ws.Cells(i, remove_duplicate_column).Value = ""

Else

previousVAL = currentVAL

End If

Next i

Catch ex As Exception

exceptionMessage = ex.Message

End Try

Like this :

Now, Your action should look like this :

Publish your action, save and go to process now.

Your process should look like this :

Lets do the test now.

I have this excel file see below :

I want to make the remove duplicate on my column A so its my first column.

I want also to start at cell 2 and finish at cell 8

so i give these parameters in my new action see below :

now run your flow and you will have this :

Try it and let me know. If it works for you, please mark me as best answer.

Re: Clear Duplicate Values from Excel sheet VB code stage

BENDRES — Sun, 10 Nov 2024 14:35:40 GMT

Hi @Mohamad_Dakkouri,

I wanted to extend my sincere thanks for sharing the Blue Prism VB code to clear duplicate values from an Excel sheet. I tested it & it worked perfectly, meeting my requirement as expected.

Can we add sorting of sheet with Column "A" before clearing the duplicate cell.

Your support and guidance have been invaluable & this solution will greatly enhance my automation process.

Thanks once again for your help!

Best regards

Re: Clear Duplicate Values from Excel sheet VB code stage

Mohamad_Dakkouri — Sun, 10 Nov 2024 16:48:42 GMT

Hi @BENDRES

Sorting on which criteria ascending or descending ?

What if we have blank value ?

Please mark me as best answer.

Re: Clear Duplicate Values from Excel sheet VB code stage

Mohamad_Dakkouri — Sun, 10 Nov 2024 18:53:26 GMT

@BENDRES

By the way you can use the latest version of MS EXCEL VBO in DX : https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-excel-vbo

you can find this action :

Try it and let us know if it work for you mark me as best answer

Re: Clear Duplicate Values from Excel sheet VB code stage

BENDRES — Mon, 11 Nov 2024 05:27:27 GMT

Hi @Mohamad_Dakkouri ,

It meets my requirements and performs as expected.

Again, thank you for your assistance!

Best regards