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
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
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?
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
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.
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.
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu