Combine unique items and their values within a Data Table

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

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,

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

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,

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

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,

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

1 Like

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