# Find duplicate values in data table and do operation as per condition in the same table

I have one Table. It has a list of Account numbers, transaction ID numbers in the form of “DC12345” and their corresponding Interest Amounts in the form of 123,456.123 number format.

Account num, Trans ID, Interest
001, DC004074, 123456
002, DC004023, 4567789
001, DC004056, 876543
003, DC004023, 4567789

I need to work on these two conditions:

1. If I find same account number in table then sum all interest for each of the deposit IDs.

2. if I find 1 deposit ID but transfered into 2 different account then the interest should divide based on total number of accounts.

Output should be like this:

Account num, Trans ID, Interest
001, (DC004074,DC004056), 999999 ## 123456 + 876543
(002,003), DC004074, 228394.5 ## 4567789/2

Please help me with a solution, possibly LINQ query to extract the DC number and calculate the interest amount and get output. (I do not know LINQ actually)

I referred to this topic but this only solves half of my problem.

maybe better to temporary split the table into two groups

• AddCaseGroups | List(Of List(Of DataRow)) - outer the groups, inner the members
(From d in dtData.AsEnumerable
Group d by k=d("Trans ID").toString.Trim into grp=Group
Where grp.Count = 1
Select gl = grp.ToList).ToList

Similar we can create the SplitCaseGroups
(From d in dtData.AsEnumerable
Group d by k=d(“Trans ID”).toString.Trim into grp=Group
Where grp.Count > 1
Select gl = grp.ToList).ToList

And finally we can combine both with the adequate aggregation need

Hi,

I am having difficulty understanding your suggestion.

But what I did was to try the solution that is provided in the attached link for the other post.

This Post

I am getting output with only the result (Sum) and not the entire Row (with Acc num and Trans ID) like:

Account num, Trans ID, Interest
123456
4567789

Below is my code:

(From d In op_DT
Group d By k=d("Account Num").toString.Trim Into grp=Group
Let sm = grp.Sum(Function (x) CDbl(x.item("Interest").toString.Trim))

How to get all items from the row?

Where in detail is it not understood

@piyush.g

Use this Linq Query

(
From row In dt
Group row By a = row(“Column Name”).ToString.Trim Into grp = Group
Where grp.Count > 1
Select grp.ToList
).SelectMany(Function(x) x).CopyToDataTable

Preparations:

Input:

(From d In dtData.AsEnumerable
Group d By k=d("TransID").toString.Trim Into grp=Group
Where grp.Count = 1
From g In grp
Group g By k2=g(0).toString.Trim Into grp2=Group
Let  til = String.Join(",",grp2.Select(Function (g2) g2(1)))
Let am = grp2.Sum(Function (g3) Convert.ToDouble(g3(2))).toString("F2")
Let ra = New Object(){grp2.First()(0), til, am}

SplitSet

(From d In dtData.AsEnumerable
Group d By k=d("TransID").toString.Trim Into grp=Group
Where grp.Count > 1
Let  anl = String.Join(",",grp.Select(Function (g) g(0)))
Let ams = (Convert.ToDouble(grp.First()(2))/ grp.Count).toString("F2")
Let ra = New Object(){anl, k, ams}