Sum of values in a column in excel that are all related to the same single value present in another column

Help Activities
, ,
1

Hi, I have a filtered datatable that when is put in a new excel file (through write range) looks like this :

  • Column A : name of the companies
  • Column B : Invoices ID
  • Column C : value of the invoices
    Basically I have the first 7th rows with the same company(cells A1-A7), with different invoices(cells B1-B7) of different values (cells C1-C7), then the same stuff for the following 4th rows with another company that in turn has different invoices of different values and so on… I do like it but my question is : instead of creating an excel like this, wouldn’t be possible to tell "create an excel file that directly spend 1 full row(instead of 7 or 4 as mentioned before) dedicated to a SINGLE company where
  • Column A would be the name of the company (A1)
  • Column B: total number of Invoices of that company (B1)
  • Column C: total value of the Invoices present in column B (C1)?
    I am quite new to uipath and programming in general, so i feel like i have to do some sort of "for each Row in data table " activity but don’t know exactly what to put in the body. .
    Thanks in advance
2

Welcome to the UiPath forum @Adell90 !

Can you share the excel file or the screenshot of the scenario? Kind of confused in understanding that.

Just for your reference:
Once you read the excel using Write range then you can loop using for each data row. Inside that you can assign the row(“ColumnName”) to the variables and perform action needed, you can check the value for that and have condition in there

Also, suggest you to debug first and add a breakpoint after reading the Datatable. you will see in Local the Datatable loaded, that view would give you some hint to proceed.

Again, sharing the file or screenshot would help us to help you.

3

Screenshot (4)
Screenshot (4)543×501 9.9 KB

Here’s the screenshot (data are fake, so don’t worry about the names). Dont take into account Column B for now. So basically what I want to automate is " filling a row for each company that has a different name". For example, the output for “Unit&Products Inc” in the first row would be:

  • under ClientName → Unit&Products Inc
  • under Invoice ID → the total number of invoices related to Unit&Products Inc
  • under Invoice Value → the sum of all the invoices that are related to Unit&Products Inc
    In this manner instead of having 7 Rows related to “Unit&Products Inc” where each represent an invoice, I’d have 1 row representing the whole situation of “Unit&Products inc”.
    In the 2nd Row I would have the same for “Travels Print”, in the 3rd for “Teamatix” and so on.

Hope this can help understanding!
Thank you in advance @rahulsharma

4

Hi doing normal way u can try like this

  1. Read the excel and store in dt1 (datatable variable)

  2. Create a datatable with build datatable activity with required headers u need in final excel (company name, total number of invoices, total values of invoices) and store in dt_final

  3. Now use for each row to loop through each row under dt1.DefaultView.ToTable(True,“Column A”)

Now inside the loop do the following things

  1. Filter the dt1 with row(“Column A”) and store in dt2 variable.
  2. Now use a assign activity to add the following things

total_invoices= dt2.Rows.Count
total_value_invoices= dt2.AsEnumerable().Sum(Function(r) If(r(“Column C”).ToString.Trim=“”,0,CDbl(r(“Column C”).ToString))

  1. Now use a add data row activity to add the follwoing row to the dt_final datatable

{row(“Column A”).ToString,total_invoices,total_value_invoices}

After the loop , write the datatable dt_final to an excel using write range.

Another way would be use Groupby linq query to try this

Regards,
Nived N

5

@Adell90
prepare the target datastructure with a

  • build datatable activity
    • model the 3 cols: ClientName, InvoiceCount, Sum (feel free on col names) -
      output/Variable: dtResult

Use an assign acitvity
Left side: dtResult
Right side

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("ClientName").toString.Trim into grp=Group
Let s = grp.Sum(Function (x) CInt(d("InvoiceValue").toString.Trim))
Let ra = new Object(){k, grp.Count, s}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

with these two steps you will create the report as specified above. Feel free to read more about Grouping Data and LINQ here:

7

total_value_invoices= dt2.AsEnumerable().Sum(Function(r) If(r(“Column C”).ToString.Trim=“”,0,CDbl(r(“Column C”).ToString))

Could you explain me what are you doing here step by step? i have difficulty understanding this part. I got the rest!

8

it sums up the values from col c and checks if it is empty string or not.
In case of an empty string it will use 0 as col value

this will avoid the invalid value exceptions from CDBL when empty string is passed

1 Like
9

I see. As I said I am very very new and don’t understand a lot about it , I am trying to learn and I ended up having errors.
First of all, to let you better understand (maybe here’s the mistake I made) the screenshot I sent was of an excel file (called “Risultati” ) which was the output of doing a “write range” activity. Since I wanted to follow @NIVED_NAMBIAR plan I started from the DataTable stored in Uipath (deleting the write range activity) with the same values shown in the screenshot. After that I just followed said plan. Despite being clear, I don’t know what to put as “operation” (and the following value) in the configure filter ( step 3 part 1). Furthermore when using “assign activity” for Total_value_invoices I received this error : cannot assign from type ‘System.Double’ to type ‘System.String’ in assign activity ‘Assign’

I lack programming skills probably and that’s why I am having many issues. I am following the advanced plan to take the certificate. If you have any tips also just send them my away, I’ll gladly take them.
Thank you in advance,
Alessandro

10

can you share the xaml with us we will check and possibly edit it

11

Hi @Adell90

The correctio u had to do is u had to make the variable as System.Double datatype instead of string

I mean for Total_value_invoices variable.

Hope this helps u

Regards,
Nived N

12

Screenshot (5)
I am almost there. The only problem is that it doesnt actually calculate the total number of Invoices (Column B) and the total value of the invoices (Column C) for each company but rather the total of all of them. 26 in fact is the sum of the of invoices of all the companies.
here’s the xaml
Main.xaml (18.0 KB)

13

do this that you will filter for the client groups
grafik

14

Thank you @ppr and @NIVED_NAMBIAR It worked out Finally! I think I’ll train myself also with the solution that you Peter gave me with the group. I have one more question though related to this formula:

“r” is arbitrary ? I could have chosen s or x without any issue whatsoever, right?

15

yes, we can treat it like a local variable name

For LINQ also have a look here:

16

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