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

Hi,

I am trying to remove Data row from Two different datatable if “Person Number” column is Match and Remove Row in Excel file

Please see my Condition below

image

thank you in advance
-Vincent

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!

1 Like

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

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.

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

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

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

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

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

Bot Report_10222021.xlsx (11.3 KB)

hi @rahulsharma

thank you

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)

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?

file is accessed once for reading and once for writing.

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

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

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!

1 Like

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.

hi @c0Ld can you share a sample workflow pls?

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

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

2 Likes

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

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

1 Like