Getting duplicate vales sum in column B with all duplicate values in column A

[Venkat_Automation_123 (Venkat)]

My excel has three columns columA column B column c columA used as reference and column B also have duplicate values they are grouped and added values in column c .

Column A Column B column c
123 212 6
123 212 7
123 516 8
123 516 10
123 917 12
789 677 19
986 678 7
986 678 9

Output datatable

123 212 13
123 516 18
123 917 12
789 677 19
986 678 16

Pl help me

[Yoichi]

Hi,

Hope the following sample helps you.

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ColumnA").ToString,r("ColumnB").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) Int32.Parse(r("ColumnC").ToString))},False)).CopyToDataTable()

Sample20220814-2.zip (2.6 KB)

Regards,

[Venkat_Automation_123 (Venkat)]

Thanks for fast rply but i want one by one values in column c to update on application and update in the excel as well… Can you please suggest be that looping method

[Yoichi]

Hi,

Can you please suggest be that looping method

Basically, we can use ForEachRow activity to iterate the group as the following, for example.

If the above is not your requirement, can you elaborate?

Regards,

[Venkat_Automation_123 (Venkat)]

Hi Yoichi,
Thanks for your fast reply, very thankful to your solution, your solution is working.

But I have another column also named status and after adding values in column c that values are checked for the values in the app if same then the status is written to the column status in the input file at last the output file is look like shown in below fig. Is there any way to update the status like shown in fig .

Also I have negative values in column c and I want to remove that the negative sign, whether it is negative or positive I want to add the both values i.e the absolute value and compare the value in the app.

Ex: In the column 5&6 column c after adding the sum is 16.353.

Thanks and Regards,

[Yoichi]

HI,

Perhaps you should raise a new topic because the above is away from title of this topic and it helps someone who has same problem.

Anyway, if you need to sum values which is removed minus sign, Math.Abs works, as the following.

dt = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ColumnA").ToString,r("ColumnB").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) Math.Abs(Double.Parse(r("ColumnC").ToString)))},False)).CopyToDataTable()

Then check compare it with application value, and write result to original datatable using value of ColumnA and ColumnB.

Or it might be better to use Dictionary<Tuple<String,String>,Double>

dict = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ColumnA").ToString,r("ColumnB").ToString)).ToDictionary(Function(g) g.Key,Function(g) g.Sum(Function(r) Math.Abs(Double.Parse(r("ColumnC").ToString))))

Regards,

[Venkat_Automation_123 (Venkat)]

Thank you Very Much Yoichi , It worked