Combine unique items and their values within a Data Table

[NervYerem]

I’m trying combine items in a data table and their values in the next cell.

I can disregard the first column, date doesn’t need to be considered at this point.

Example Data table:

Column0	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12
11/1/2021	Task1	4					Task1	3			Task1	1
11/2/2021	Task3	4.5	Task2	1	Task3	8	Task1	3			Task3	2
11/3/2021	Task1	4	Task2	2			Task3	4			Task1	2
11/4/2021	Task1	5	Task3	5	Task2	1	Task1	3			Task3	1.5
11/5/2021	Task1	1.5	Task3	9			Task3	3			Task1	1.5
11/6/2021					Task3	5
11/7/2021	Task1	6	Task2	6			Task1	8	Task1	3
11/8/2021			Task2	5			Task1	3			Task1	1
11/9/2021	Task3	4.5	Task2	1	Task3	6	Task3	1

Example Output:

Column0	Column1
Task1	46
Task2	16
Task3	50

[Yoichi]

Hi,

Can you try the following?

dtResult = Enumerable.Range(1,dt.Columns.Count \ 2).SelectMany(Function(i) dt.AsEnumerable.Select(Function(r) {r(i*2-1),r(i*2)})).Where(Function(x) not String.IsNullOrEmpty(x(0).ToString)).GroupBy(Function(x) x(0).ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(z) double.Parse(z(1).ToString))},False)).CopyToDataTable

Sample20211124-2.zip (9.7 KB)

Note : The result is not same as your output. Can you check it?

Regards,

[NervYerem]

It seems to error out looking for a column that is +1 than the columns that exist.

There were 37 columns in data table I had applied this to, and this was the error I received:
Error: Cannot Find Column 38

[Yoichi]

Hi,

I tried my workflow with data which has 37columns(this means column0 - column36 as the following sample), and it works without exception. Can you check if there is extra column except target table?

Sample20211124-2v2.zip (15.7 KB)

Regards,

[NervYerem]

Sorry, I was incorrect before. My data table has 38 columns (column0 - column37).
And the error I receive is “Cannot find column 38”.

[Yoichi]

Hi,

In your first post, there is date column at column0 and remaining is pairs of name and value. So number of columns is always odd, isn’t it?
If your table isn’t the same as above structure, can you share it?

Regards,

[NervYerem]

Thanks! You are correct, I was getting an extra column that was throwing things off. After I avoided that, your solution worked!