Deleting an Excel Data Row if it contains the same specific data

Help
,
1

Hello,

I am having a little trouble with a process I’m developing. I need to take a user ID, name and email from a spreadsheet, and email the user. If a user ID is the same as the previous, the item in the spreadsheet needs to be removed. For example,

Excel_Example

The row in red shows the same user ID as the one above it, so the row in red needs to be removed. How would I go about doing this? I am completely stuck, so any input would be helpful.

Thanks in advance!

1 Like
2

Hi @william.coulson,

Please use remove duplicate rows activity ( it will delete all the duplicate row in the data table) or inside the filter use remove data row activity.

Regards,
@Spark_robot

3

Hey @Spark_robot,

This works for one ID, but there are 200+ rows in my Excel Spreadsheet and when it repeats, it will only be two or three rows that have the same ID. I tried to use the remove duplicate rows activity and nothing was removed, and I’m unsure of what to use in the remove data row activity as I am unsure of what row numbers the duplicate rows are in.

4

Hi @william.coulson,

Can I get the sample data from you for am able to resolve?

Thanks & Regards,
@Spark_robot

5

Hi @william.coulson,

Try with the below option:

yourDataTable.AsEnumerable().GroupBy(Function(x) x.Field(Of Integer)("ID")).[Select](Function(y) y.First()).CopyToDataTable()

This will copy the filtered rows alone to a new datatable, you have to use this in a Assign Activity. Then write the data back to the excel if you need.

Secondd option:
Creating a default view with distinct

yourDataTable.DefaultView.ToTable(True, "column1", "column2", "column3")

This will return you a datatable, so you need to have a assign activity with a datatable on the left

1 Like
6

@Spark_robot, I can’t share the sample data as it contains sensitive information I’m afraid

@sarathi125, I will give this a go, thanks

7

@william.coulson
sometimes while readin Excel into Datatable some characters e.g. a blank on the end is transported as well. As its not remarked without explicit checking so this is quickly unknown.
However this leads to the sitation that some filter, distinct activities are returning a unexspected result.

have a try with this:
datatableVar.AsEnumerable.Distinct().CopyToDataTable (maybe will have same result)

For taking more control maybe this can be done (as a Variation to @sarathis125 GroupBy Approach)

1 Like
9

@ppr,

I looked at the spreadsheets properly, it has taken out every row if the ID is a duplicate (that would work for the user you made a workflow for previously) but I need to keep one row of the duplicate IDs. For example, if there are three rows that contain the same ID, I would need to keep one, and discard two. Any ideas?

Many thanks

10

Hello,

Resolved the issue myself. I used a long winded version instead that definitely works, which was installing Ablebits Suite Add-Ins the Ablebits website, and then using the attach window activity to attach the spreadsheet and use click and select item activities to merge the cells. Thank you all for your help, it has been much appreciated.

1 Like
11

@william.coulson great news. Sorry for delayed answer currently i am on travelling

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.