Convert the format and sum the values

I am having data in the shown format:


The output required is in the below format:


Here, the Category column unique values are added as the column names and then for every Item No. sum of Contribution is done accordingly. Like RC100 has RM-DU Sum = 35 +50 = 85 and so on and at last total column is added which does the row wise sum.

The excel file is attached here Convert and Sum.xlsx (9.2 KB)

Please help.

Hello @Swara_Soni,

Item no’s will be the same or there are more item no’s???

Thanks & Regards,
Raj Parsana


@raj.parsana , this is a dummy datasheet. The actual data contains more rows. But the Category column unique values does not exceed than 10 in actual data also.

Hi @Swara_Soni,

A approach can be ,

Considering this sheet a solution can be

  1. Build a datatable for the output as if with headers Item no., RM-DU,RM-ST,Total
  2. On the main sheet here sheet1 use a for each loop in that loop first extract 1st Item no.
  3. Inside Loop use Filter datatable activity , the inputs in the activity can be rows containing the item no. like row(Item No).tostring and secondly filter along on category column firstly for RM-DU(in first iteration).
  4. The output datatble you will get would have only the common values like table with values of RC100 (Do the same for RM-ST as well).
  5. Now sum up the column contribution of that table with a function like dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal)("Contribution"))
  6. Then finally add that data into your builded datattable using a for each loop in which you will use a add data row and give the specific Loop variable values to it.
  7. and now you have to just print that .


find below some starter help. It is similar close to your case and should be compatible. Just configure in the Variables the different ColumnName Settings. The result DataTable Structure is created dynamicly on the different dictinct values found in Category

Fast Prototype in LINQ:
GroupBy_1Col_SumColN-SplitColNTotals.xaml (10.7 KB)

Decomposed Fast prototype demo:
GroupBy_1Col_SumColN-SplitColNTotals_Decomp.xaml (16.1 KB)

1 Like

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