How to sum group by values in excel data

Help Studio
, ,
1

i have an excel data below

Name Qty
Pencil 5
Rubeer 12
Pencil 34
Pen 23
Rubeer 8
scale 66
scale 41
Rubeer 48

i want to sum group like below and write in new sheet
name total
pencil 39

Forum Engagement Daily Reports
2

You can prepare a Datatable with the grouped and sum up values, then you can filter or postprocess it and writing back the result to excel

Assing Activity:
dtGroupSums | DataType: DataTable =
YourDataTableVar.Clone()

Assign Activity:
dtGroupSums=

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("Name").toString.Trim.ToUpper into grp=Group
Let gs = grp.Sum(Function (x) CInt(x("QTY").toString.Trim))
Let ra = new Object(){k,sm}
Select r = dtGroupSums.Rows.Add(ra)).CopyToDataTable

Also have a look here:
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

UPD-1: Attached Training Resource

2 Likes
Forum Engagement Daily Reports
3

Hi @anand_kumar4

use read range to load excel data into dtInput. then assign to run LINQ into dtResult.

dtResult = (From row In dtInput.AsEnumerable()
Group row By k = row(“Name”).ToString Into grp = Group
Let total = grp.Sum(Function(r) CInt(r(“Qty”)))
Select dtInput.Clone().Rows.Add({k, total})).CopyToDataTable()

Use Write Range to write dtResult to a new sheet named Summary.

For more learning ref. above link shared by ppr.

Happy Automation

1 Like
4

To sum and group data by “Name” in UiPath:

  1. Read Range to read data from Excel into a DataTable (dtInput).

  2. Use Assign activity with this LINQ query to group and sum:

    dtGrouped = (From row In dtInput.AsEnumerable()
             Group row By Name = row("Name").ToString()
             Into Group = Sum(CInt(row("Qty")))).CopyToDataTable()

  3. Write Range to write dtGrouped to a new sheet (e.g., “Sheet2”).

This will group by “Name”, sum “Qty”, and write the result to the new sheet.

1 Like
5

Hi @anand_kumar4

Use Read Range to load the Excel data into a DataTable as dtInput.

Use Assign activity to build dataset using LINQ:

dtResult = (From row In dtInput.AsEnumerable()
Group row By key = row(“Name”).ToString.Trim().ToLower() Into groupData = Group
Let totalQty = groupData.Sum(Function(r) CInt(r(“Qty”)))
Select dtInput.Clone().Rows.Add({key.ToUpper(), totalQty})
).CopyToDataTable()

Use Write Range to output dtResult.