Adding a subtotal row based upon like names

I have tried the balareva group by aggregation and it does not work. I have tried joins and it does not work. Any help anyone can give would be wonderful. In the attached data
example.xlsx (9.2 KB) I am trying to sum by Employee and insert a subtotal below that group of line items for each employee aggregating Allocated Expense Amount. I think I have tried just about everything and am to the point where I am telling the business this is not possible. Does anyone have any ideas?

Can you give an example of your expected output you are trying to achieve?

It seems like you just want to use the excel “subtotal” function, is that correct? If so, you can use a simple macro and an ‘execute macro’ activity if it needs to be run by uipath. All the macro would do is create a subtotal (data–>subtotal) and selects at each change in ‘employee’, use function ‘sum’, add subtotal to ‘Allocated Expense Amount’

The VBA code would be something like this (I would make this more resilient, but this can get you started; made by using the vba macro recorder):

Range("A1").Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(10), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True

If you need it to be done all in RPA, this is a little harder because group by aggregation doesn’t subtotal, it combines everything. You can emulate the subtotal by combining a few linq queries, but it’s a lot more complicated than just using the built-in solution easily available within excel

Hi @Chris_Bolin ,

This can be achieve through logic:

Steps:

1 - Read excel
2 - Iterate each item of data table.
3 - Check if previous row employee <> Current row employee then add new row with totoal.

Whole steps need to be created in Workflow so that you can easily check previous row value with counter ex- dt.row(iCounter-1)(“Columnname”) (To get previous row value)
dt.row(iCounter)(“Columnname”) (To get current row value)

This is a good idea but having some confusion on how to set that up. I read the Dt (formatDt) and established a counter irowCounter = 0. Inside the for each loop iterating through formatDt I incremented irowCounter +1. Here is where I don’t think I have the syntax correct

In an If I put (irowCounter -1)formatDt.row(“Employee”).toSTring <> (irowCounter)formatDt.row(“Employee”).toString

But I am getting an error for “End of Expression Expected”. Am I missing something?

@Chris_Bolin , Do you want sum “allocate expenses amount” based on employee?

For example:- Employee1- 19.6 (total of two line items)

yes - that is the goal.

Ok will share a .xaml file for you

thank you

example.xlsx (9.2 KB)

employee_sum.xaml (8.0 KB)

Hi @Chris_Bolin
Please check and let me know

Thanks my friend but this is not working as expected. It provides the sum yes. The issue is that the client desires this to be written as a subtotal inserted under a summary for like names. When your solution is sent as an output it only provides a sum of all items not by employee even though the write line shows to totals. The desire is something along this line

First value of employee1
Second value is employee2

Printing output it is depend on requirements

You can create a new excel with summary rows. You have to add data row and write the total. That’s it

output.xlsx (7.6 KB)
employee_sum.xaml (9.4 KB)
example.xlsx (9.2 KB)

Here we go

1 Like

This is perfect! Thank

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