topic RE: Delete duplicate row in data table in Product Forum

Delete duplicate row in data table

Lily.L — Thu, 11 Jan 2024 01:59:00 GMT

Hi,

I have not found a correct answer to this question can you please help 🙂

I want to delete duplicate rows across all columns in the table. My table has some blanks rows

I have seen the removes duplicates function; I have tried but it doesn't work. I delete everything except the headers and the 1st row....

And, this is the code stage in the object. I don't know about coding so I do not prefer changing any code.

Try

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

wb = GetWorkbook(handle, workbookname)
ws = GetWorksheet(handle, workbookname, worksheetname)

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

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

result = True

Catch ex As Exception

result = False
message = ex.Message

End Try

------------------------------------------------------------------------------

I have an example. The result is not the same as what Excel provide and I don't know why......

Pers.No.	Personnel Name	Item Type
80000	Joe	E-Mail
80004	Fred	Supervisor ID and Name
80004	Fred	E-Mail
80004	Fred	Manager ID and Name
80006	Sarah	Supervisor ID and Name
80006	Sarah	E-Mail
80004	Fred	Manager ID and Name
80006	Sarah	Manager ID and Name
80007	Emily	Supervisor ID and Name
80007	Emily	E-Mail
80006	Sarah	Supervisor ID and Name
80007	Emily	Manager ID and Name
80008	Cameron	Supervisor ID and Name
80008	Cameron	E-Mail
80008	Cameron	Manager ID and Name

------------------------------
Mia Mikela
------------------------------

RE: Delete duplicate row in data table

sumire — Thu, 11 Jan 2024 03:37:00 GMT

Hello @Mia Mikela,

Try this asset in DX

https://digitalexchange.blueprism.com/dx/entry/127021/solution/generic-data-table-filter-utility

Hope this asset helps you

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: Delete duplicate row in data table

Lily.L — Thu, 11 Jan 2024 07:43:00 GMT

Hi Mitsuko, thanks for recommending but it's not working for my case.

------------------------------
Mia Mikela
------------------------------

RE: Delete duplicate row in data table

sumire — Mon, 15 Jan 2024 10:05:00 GMT

Hello @Mia Mikela,

So you want to delete "rows in a table where all the entries are exact matches"?

In other words, am I correct in understanding that the example you provided, rows 9 and 13 are correct to be deleted?

I propose to create an action like this:

The code stage 'Remove Duplicate' contains only one line as follows:

outCollection=inCollection.DefaultView.ToTable(true)

Input Argument is 'inCollection'

Output Argument is 'outCollection'

This is a simple code stage, so even if you are not familiar with coding, it will be easy to create.

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: Delete duplicate row in data table

MichealCharron — Mon, 15 Jan 2024 13:16:00 GMT

@Mia Mikela

We don't use that VBO so I have never tried out that action but I do know that Excel's array indexing is 1-based. You are using 0-based indexing for the columns. You could give 1-based indexing a try and see how that works for the action.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------