Identify the Duplicates and sum the value

Hi,

1.I need to find the duplicate value in the “ID” column.
so value 80 and 82 contains duplicates.
From that i need to compare “key” column.If duplicate is there in the key column then i need to sum all the columns value.
In this case 82 column as duplicate ID and also key column contains duplicate key so i need to sum and bring the value as

image

Input:test.xlsx (9.4 KB)
image
I need to find duplicate for ID and key column.if duplicates ID contains duplicate key.Then i need to sum Value1 ,Value 2 and value 3.

1 Like

@Sob
Thanks for opening a new topic.
I moved the from old post the first answered part here.

detecting the duplicates:
For detecing the relevant values (Col ID, Col Key) of the duplicates I would suggest for:

Using an assign activity
Statement:
(From r In dtSample.AsEnumerable()
Select C1 = r(0).ToString.Trim, C2 = r(2).ToString.Trim
Group By C1, C2 Into Group
Select C1, C2, Count = Group.Count
Where Count > 1
Select New String() {C1,C2}).ToList

returns a List of String()

The second part could be done in a classical way within a for each acitivty, taking care about the summing up (refer to some suggestions on above). But I will have a look on it later on more short solution

1 Like

Thanks @ppr
Will you able to share your XAML file.

@Sob
I will do, but give me some time, please. Project is at home’s laptop

1 Like

Ya sure…Thank you :slight_smile:

@Sob
Good News I got it fully running :slight_smile:
Here is the sample Workflow
LINQ_2KeyGroupIn.xaml (8.6 KB)

Here a short explanation how it is implemented

Read Range Source Excel File - getting a datatable

Assign - returning the informations on the so called duplicates:
returns a List of String()
Statement:
(From r In dtSample.AsEnumerable()
Select C1 = r(0).ToString.Trim, C2 = r(2).ToString.Trim
Group By C1, C2 Into Group
Select C1, C2, Count = Group.Count
Where Count > 1
Select New String() {C1,C2}).ToList

Assign - returning the result as per requirement:

I need to find duplicate for ID and key column.if duplicates ID contains duplicate key.Then i need to sum Value1 ,Value 2 and value 3.

Requirements for Column Name was unclear so i return the value from first row of duplicate (xxx) similar to your origin requirement

Statement:
(From d In Duplicates
Let rows = dtSample.AsEnumerable.Where(Function ( r ) r(0).ToString.trim.equals(d(0)) And r(2).ToString.trim.equals(d(1)))
Select New Object() {
rows(0)(0).ToString,
rows(0)(1).ToString,
rows(0)(2).ToString,
rows.Sum(Function ( r ) CInt(r(3))),
rows.Sum(Function ( r ) CInt(r(4))),
rows.Sum(Function ( r ) CInt(r(5))),
rows.Sum(Function ( r ) CInt(r(6)))
}).ToArray

Returns an Array of Object()

Assign - preparing a second database (dtResult) by using the same table structure from origin
Statement: YourDataTableVar.Clone

For each / add DataRow
Iterating over the Array of Object() - the calculated rows and adding this to dtResult

5 Likes

Thanks a lot @ppr
Its worked perfect :slight_smile:

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