How to remove Duplicate row Based on "person number" from Two different data table While looping through

[rahulsharma]

This will get you the common rows from both Datatables in a new datatable

dtCommomElements = dt1.AsEnumerable().Where(Function(row) dt2.AsEnumerable().Any(Function(x) x(“Person Name”).ToString=row(“Person Name”).ToString)).CopyToDatatable

And then you can then delete these rows from the original two datatable using Linq

dt1=dt1.AsEnumerable().Where(Function(row) NOT dtCommonElements.AsEnumerable().Any(Function(x) x(“Person Name”).ToString=row(“Person Name”).ToString)).CopyToDatatable;

dt2=dt2.AsEnumerable().Where(Function(row) NOT dtCommonElements.AsEnumerable().Any(Function(x) x(“Person Name”).ToString=row(“Person Name”).ToString)).CopyToDatatable;

Hope this helps!

[Vincent_Nuestro]

hi @rahulsharma

thank you again for your response,

i dont know how this works, but when i tried i got an Error, Should i put it inside for each row?
or Did I Missed something?

dtDuplicates = dtEffDateError.AsEnumerable().Where(Function(row) dtReport.AsEnumerable().Any(Function(x) x(“Person Number”).ToString=row(“Person Number”).ToString)).CopyToDatatable

dtEffDateError = dtEffDateError.AsEnumerable().Where(Function(row) NOT dtDuplicates.AsEnumerable().Any(Function(x) x(“Person Number”).ToString=row(“Person Number”).ToString)).CopyToDatatable

dtReport = dtReport.AsEnumerable().Where(Function(row) NOT dtDuplicates.AsEnumerable().Any(Function(x) x(“Person Number”).ToString=row(“Person Number”).ToString)).CopyToDatatable

[rahulsharma]

@Vincent_Nuestro

Should i put it inside for each row?

no. no for loop required.

The error states there are no rows in the source.
Make sure the input datatable are not null. Do check that…

Also For each can also be implemented if you wish to go for that approach.

Use a Join Datatable, with the column name as Person Name
This will give you common rows in both input datatable, output will be a datatable dtCommomElements

then use a for each row loop for this dtCommonElements, inside it put 2 filter datatable activity, one for each of your input datatable. Then in the filter wizard, just put the condition column “Person Name” value = row.toString

this will remove the common elements from both the datatable.

[Vincent_Nuestro]

hi, I think i describe it wrong?

Note: is is a Dynamic data, It always change @rahulsharma

I wanted to delete the ROW that Contains person number that match from Datatable1 to Datatable 2.

Or if Person number from datatable 2 is Match in datatable 1.

Please see my datatable below.

Here is my data table 1, it contains person number "101010"

Data Table 2

Now, my expected result is to Delete the Row from Datatable 1 and datatable2 that contains “Person Number” 101010

[Vincent_Nuestro]

i tried another method Solution from another Topic, but if did not work on me.

it did not delete any row from my data table

How to delete datarow in datatable?

Yah the expression is fine but we cannot use REMOVE DATAROW ACTIVITY inside thr for each row loop as we are currently iterating through each row in the datatable and meanwhile we won’t bale to delete the rows in it and that why Ymir’s was showing that error. So we need to get the row index now using add to collections activity and use a separate for each loop activity and pass the variable used in the add to collections- as input here in for each loop activity and then use remove datarow activi…

[Vincent_Nuestro]

hi @rahulsharma can you show sample workflow on this? please.

[rahulsharma]

sorry for the late response.

Sure mate, i can show you sample workflow.

Could you please send that file? That eill help you to understand the workflow better

@Vincent_Nuestro

is is a Dynamic data, It always change

it is fine. That should not affect the logic. are you getting any error?

[Vincent_Nuestro]

Bot Report_10222021.xlsx (11.3 KB)

hi @rahulsharma

thank you

[rahulsharma]

This should help.

We get the elements which are common in those two datatable and then remove them from original record. I pasted the values, run this and the filtered values will be in the same excel in two different and new sheets

FilterForum.zip (12.2 KB)

[Vincent_Nuestro]

hi @rahulsharma thank you for this,

But there is a thing that i worried about. there is a lot of Workflows activity using and reading that sheet “Encoded < 14 Days” and “Failed” before and after removing duplicate row.

is it possible that i can remove it directly from that sheet without create an new excel sheet?

[rahulsharma]

@Vincent_Nuestro

using and reading that sheet “Encoded < 14 Days” and “Failed”

file is accessed once for reading and once for writing.

@Vincent_Nuestro

is it possible that i can remove it directly from that sheet without create an new excel sheet?

yes you can, just mention the same sheet name and the range as “A1” this will replace the existing data there

[Vincent_Nuestro]

hi it, i think it did not work
i mention the same sheet name and the range as “A1”

[rahulsharma]

to clear the content you can either use the Excel Application scope and then use delete range, mention the max range you can possibly give to make sure the entire sheet is deleted.

that will require excel installed on your system.

OR

you can also delete that sheet and write with the same name, that’ll create a new sheet of same name

FilterForum.zip (22.2 KB)

Hope this helps!

[c0Ld (Luís Silva)]

Hi,

I had a similar issue and I resolved by making a list of IDs and run for each ‘data table’.
I hope that could be helpful.

[Vincent_Nuestro]

hi @c0Ld can you share a sample workflow pls?

[Vincent_Nuestro]

hi @rahulsharma Delete Range activity is not working on my UIPATH studio version 2018.4.1

[rahulsharma]

you can use a VB script also to delete it.

Attached the script in the code in new sequence, you can use that.

Make sure you do the necessary changes required for running the macro. You need to update the below setting in Excel Settings

Steps:

File:
FilterForum.zip (23.8 KB)

Hope this should help you resolve your query. If so, mark a post as solution to close the thread

[Vincent_Nuestro]

hi @rahulsharma your VB Script is working already, but it writes same data that i need to delete from the table.

[Vincent_Nuestro]

hi @rahulsharma Thank you so much for your help and patience its working Now:)