How to combine string for each rows and sum amount and group by "Column Name"

[Htwe_Ko_Ko_Wynn]

Hi all, let me know how can I combine string values in “CLASS” column and sum “settledcommission” by Grouping “AGENT CODE” to get following data table result

The original data is attached as follow;

[Yoichi]

HI,

Can you share your worksheet as a file? it’s no problem if dummy data.

Regards,

[Htwe_Ko_Ko_Wynn]

Hi Yoichi,

Kindly check the attached file for dummy raw data.
for_testing.xlsx (8.1 KB)

[Yoichi]

Hi,

How about the following? This outputs result to sheet “Result”

dt = dt.AsEnumerable.GroupBy(Function(r) r("AGENT CODE")).Select(Function(g) dt.Clone.LoadDataRow({String.Join(",",g.Select(Function(r) r("CLASS")).Distinct),g.Key,g.Sum(Function(r) Double.Parse(r("settledcommission").ToString)) },False)).CopyToDataTable

Sample20220916-1.zip (9.8 KB)

Regards,

[Htwe_Ko_Ko_Wynn]

@Yoichi

dt.AsEnumerable.GroupBy(Function(r) r("AGENT CODE")).Select(Function(g) dt.Clone.LoadDataRow({String.Join(",",g.Select(Function(r) r("CLASS")).Distinct),g.Key,g.Sum(Function(r) Double.Parse(r("settledcommission").ToString)) },False)).CopyToDataTable

Thanks, this solution works for me.