How do I sum values from different rows that have the same variable?

Help Activities
,
1

help

Hello, how can I sum up the $ spent of the same month?

2

Hello,

Look at this post, I think it can help you :slight_smile:

1 Like
3

Hi @helpplease

You need to apply grouping logic over the month column.

1 Like
4

Hi @helpplease

May be this thread will help you solve the query:

Regards,

1 Like
5

@helpplease

  1. Read the data into a DataTable variable from a source (e.g., Excel, CSV, database) using the “Read Range” activity.
  2. Create a new Dictionary variable to store the summed values. The key of the dictionary will be the variable you want to group by, and the value will be the sum.
  3. Iterate through each row in the DataTable using a “For Each Row” activity.
  4. Inside the loop, check if the variable already exists as a key in the dictionary. If it does, add the current row’s value to the existing sum. Otherwise, add a new key-value pair to the dictionary with the variable and the current row’s value.

Hope it works!!

1 Like
6

Since your reading it has a datatable,
Using foreach row in datatable you can check if the currentRow(“Month”) equals dt_Input(forEachIndex-1)(“Month”), if true then SpentAmt = currentRow(“Spent”) + dt_Input(forEachIndex-1)(“Spent”). Else (that is the current iteration Month is not equal to previous iteration Month) SpentAmt = 0.

Note: -

  1. To get forEachIndex, for the activity for each row in datatable, open the properties panel and under index, using Ctrl+K give the name forEachIndex to create index.

  2. Also SpentAmt is a variable that can be used within the for each row in datatable activity.

  3. If you want to use it outside for each, you can create a dictionary and in else part ( when month is not equal) set key as dt_Input(forEachIndex-1)(“Month”) and Value as SpentAmt. Then set SpentAmt as 0

1 Like
7

Hi @helpplease ,

Considering DT as the Datatable received after reading the Excel Sheet. We can check the below Steps :

  1. Create a Output Datatable, say OutputDT having the same 2 columns, so we clone the datatable using an Assign activity.
OutputDT = DT.Clone
  1. Next, we can perform the Group By and Sum operation on the Datatable to get the resultant datatable.
OutputDT = DT.AsEnumerable.GroupBy(Function(x)x("Month").ToString).Select(Function(x)OutputDT.Rows.Add(x.First.Item("Month").ToString,x.Sum(Function(d)CDbl(d("$ spent").ToString)))).CopyToDatatable

Let us know if you are not able to perform the above steps.

1 Like
8

It worked, thank you for showing how to do it with the variables from my table. Thank you so much!

1 Like
9

Dummy data result
Dummy data result712Ă—247 6.78 KB

^this is the result

I tried to write range at A1, thinking that it will overwrite the whole oringal table but it didn’t. The grey highlighted cells from the original is still there.
how to overwrite completely

Is there a way that i can make UiPath overwrite the original table completely?

10

You can try using the delete sheet activity to delete the sheet before writing and then use write activity to write the data in the sheet at A1 position

1 Like
11

Ok, it worked. Thank you!

12

Glad to be of help :grinning:

1 Like
13

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