How to sum the data in excel based on the product?

Learning Hub Academy Feedback
, , ,
1

Hi,

I have an excel sheet with product and cost. I want to sum the cost based on product. Is it possible?

Example: Mobile-2000
Lap-3000
Mobile- 5000.

I want to get output : Mobile 7000
Lap 3000 in the excel sheet.

Could anyone help me on this?

Thanks.

2

@sreeprasad

Refer this post it will help you

Regards,
Mahesh

3

Hi Mahesh,

I’m new to Uipath and I don’t know where to give this query. I got trial version today and I have to analyse and give a demo tomorrow. I’m able to fetch the data from excel sheet and copy into another. While copying where I have to give the above query to get the summed output. Could you please help me on this?

Thanks

4

@sreeprasad

Just change the name of the column based on which you have to group and name of the column of the price to get the Sum. Use the below query in Assign activity

Dictionarty=( From p in dta.Select
group p by Department=p.Item("PRODUCT") Into GroupA=Group
Select GroupA).ToDictionary(Function(x) x(0)("PRODUCT").ToString, Function(x) Convert.ToString(x.Sum(Function(y) Convert.ToDouble(y.Item("PRICE").ToString))))

Dictionary is the variable of Dictionary(Of string, String)
dta is your Excel Sheet, stored in a datatable by using Read Range Activity.

Now you can access the the total sum of the product by like this

Dictionary(“Mobile”) to get the total sum of price of mobile category. and it will return 7000 as per your Example.

Regards,
Mahesh

5

Hi Mahesh,

Could you please check whether this is correct. I have attached the screenshot of query and requirement in attached image. Could you please check on this?

Thanks.

1
11772Ă—1164 83.2 KB

6

Hie @MAHESH1,
Suppose i have a null rows in “WorkHr” its throwing exception input string not in correct format.
How to resolve this any help.

Thanks In advance.

7

@balu
You are not doing any operation in WorkHour Column in that query then how it will throw exception.

Can you please share the screen shot of excel and Query

Regards,
Mahesh

8

@MAHESH1
In above example we are summing up total “WorkHr” right. if there is any empty row in that column its throwing the error.“Input string not found”

9

@MAHESH1

example:
Product Price
Mobile- 2000
Lap-
Mobile- 5000
Lap- 3000

and using below query, since i have a null value in price its throwing the error.How to handle it.

Dictionarty=( From p in dta.Select
group p by Department=p.Item(“PRODUCT”) Into GroupA=Group
Select GroupA).ToDictionary(Function(x) x(0)(“PRODUCT”).ToString, Function(x) Convert.ToString(x.Sum(Function(y) Convert.ToDouble(y.Item(“PRICE”).ToString))))

10

@balu

We are summing up the column price
There is no working hour column here
Regards,
Mahesh

11

Yes bro “WorkHR” is in another example i just posted here,
if a row in Price is null its throwing the error Input string not found.

12

@balu

Can you please post that screen shot of excel and code here, for better understanding.

Regards,
Mahesh

13

@MAHESH1

Please find attached file

( From p In Dt1.Select
Group p By Product=p.Item(“Product”) Into GroupA=Group
Select GroupA).ToDictionary(Function(x) x(0)(“Product”).ToString, Function(x) Convert.ToString(x.Sum(Function(y) Convert.ToDouble(y.Item(“Balance”).ToString))))

Test.xlsx (8.0 KB)

14

@balu

I have not tested the code…:grinning:

( From p In Dt1.Select
Group p By Product=p.Item(“Product”) Into GroupA=Group
Select GroupA).ToDictionary(Function(x) x(0)(“Product”).ToString, Function(x) Convert.ToString(x.Select(Function(z) Where not string.IsNullOrEmpty(z("Balance").ToString).Sum(Function(y) Convert.ToDouble(y.Item(“Balance”).ToString))))

Please add this Select(Function(z) Where not string.IsNullOrEmpty(z(“Balance”).ToString) It will select only rows which are not empty

Or try like this also

( From p In Dt1.Select
where not string.IsNullOrEmpty(p.Item("Balance").ToString)
Group p By Product=p.Item(“Product”) Into GroupA=Group
Select GroupA).ToDictionary(Function(x) x(0)(“Product”).ToString, Function(x) Convert.ToString(x.Sum(Function(y) Convert.ToDouble(y.Item(“Balance”).ToString))))

Regards,
Mahesh

1 Like
15

Awsome :heart_eyes: Working Fine.
Thanks

16

@balu
Oh K Super. :grinning:
Which one you tried

Regards,
Mahesh

17

Second One @MAHESH1

18

@balu

Oh K.

Regards,
Mahesh

19

HI ,
Check it this.

Regards
Balamurugan