Clear Duplicate Values from Excel sheet VB code stage

[BENDRES]

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.

[Mohamad_747]

Hi @BENDRES 

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 ?

[BENDRES]

Hi @Mohamad_747

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.

[Mohamad_747 · Accepted solution]

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.

[BENDRES]

Hi @Mohamad_747,

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

[Mohamad_747]

Hi @BENDRES 

Sorting on which criteria ascending or descending ?

What if we have blank value ?

Please mark me as best answer.

[Mohamad_747 · Accepted solution]

@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

[BENDRES]

Hi @Mohamad_747 ,

It meets my requirements and performs as expected.

Again, thank you for your assistance!

Best regards