How to Remove Rows with Matching Column (with duplicate scenario) in Another Data Table

Help Activities
, ,
1

How can I implement this? Any activity would do but I prefer LINQ.

I have two datatables, Source_DT and Car_DT.

image

I want to remove the rows in Source_DT that have matched UID column from Car_DT. As you noticed there are duplicates on both datatables. If the duplicate row in Source_DT has another matching UID in Car_DT, it should also be removed. Also, there is duplicate row in Source_DT that does not have matching UID column in Car_DT, so it should remain on the Source_DT.

EXPECTED OUTPUT:
image

[EDITED]
Doesn’t need to be LINQ activity but it is preferable.

How can I implement this? Any activity would do but I prefer LINQ.

2

can you check your expected output. Could it be the case that a third 123A Verstappen also has to be part of the output?

3

123A Verstappen has only one match on Car_DT, so the other two 123A Verstappen should remain on the expected output.

4

[EDITED]
Doesn’t need to be LINQ activity but it is preferable.

5

grafik
done with

(From d1 In dtLeft.AsEnumerable
Group d1 By k=d1("UID").toString.Trim Into grp=Group
Let mcc = dtRight.AsEnumerable.Where(Function (d2) d2("UID").toString.Trim.Equals(k)).Count
Where Not (grp.Count = 1 And mcc > 0)
Where Not (grp.Count > 1 And mcc > 1)
Let skipper = Convert.ToInt32(grp.Count > 1 And mcc = 1)
From g In grp.Skip(skipper)
Order By dtLeft.Rows.IndexOf(g)
Select r=g).CopyToDataTable

Find starter help here:
DTLGroupBy_1Col-DupCheck-DTRChecks.xaml (11.8 KB)

1 Like
6

Oh shoot!

I was late to the party…eitherway I’ll post an alternative solution here but ppr’s is the best approach imo.

I thought of using a Dictionary for this one, just to give it a try → 

Source_DT.AsEnumerable().
	Select(Function(s) Tuple.Create(
		s("UID").ToString,
		Math.Abs(Source_DT.AsEnumerable().Count(Function(c) c("UID").ToString.Equals(s("UID").ToString)) - Car_DT.AsEnumerable().Count(Function(c) c("UID").ToString.Equals(s("UID").ToString))))).
	Distinct.ToDictionary(Function(k) k.Item1, Function(v) v.Item2)

And here is where we add items to our new Datatable → 

(From kvp In dict_cars
Let rows = Source_DT.AsEnumerable.Where(Function(w) w("UID").Equals(kvp.Key)).Take(kvp.value).ToArray()
From row In rows
Select dt_result.Rows.Add(row.ItemArray.ToArray())).CopyToDataTable()

Different approaches help you become flexible.
Keep exploring solutions and you will become an Ace RPA Developer in no time.

SelectiveDeletionofDuplicates.xaml (12.4 KB)

Kind Regards,
Ashwin A.K

1 Like
7

Thank you sir @ppr , it is working well.

8

Thank you as always sir @ashwin.ashok , your solution is appreciated too!

Regards,
Anthony Jr.

9

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