Aggregate Total

Help Forum
,
1

Hi Folks,
I have one excel file in that excel I have Fund code column and for each fund code i want to sum other columns and I want to update grand total for each fund code category. for example for general account fund code is GA and for mutual fund it is MA how i get the total for each fund code.

For reference I am attaching screenshot
please do let me know how I achieve this!
Thanks in Advance

Screenshot 2024-05-29 192854
Screenshot 2024-05-29 1928541571Ă—485 345 KB

Screenshot 2024-05-29 185541
Screenshot 2024-05-29 1855411573Ă—517 371 KB

2

Please let me know if anyone has any solution?

3

Hey Bro, this can be easily achieved using VBA code, let me know if you are looking for a vba code to achieve this.
UiPath solution

  1. read excel file using Excel Application Scope into variable Dt

  2. get unique values from your column fund code using below LINQ query
    UniqueCodes=dt.AsEnumerable().Select(Function(row) row.Field(Of String)(“Fund Code”)).Distinct().ToArray()

  3. Loop through each string in UniqueCodes array and inside the loop use below query to get all filtered columns sum based on Fund Code
    SumOfColumns= dt.AsEnumerable().Where(Function(row) row.Field(Of String)(“Fund Code”) = UniqueCodes(0)).SelectMany(Function(row) dt.Columns.Cast(Of DataColumn).Select(Function(col) Convert.ToInt32(row(col)))).GroupBy(Function(x, i) i Mod dt.Columns.Count).Select(Function(g) g.Sum()).ToList()
    the above query will give sum of each column as list of integers

  4. then use below query to get cell address where “Grand Total” is found
    CellAddress= dt.AsEnumerable() _
    .SelectMany(Function(row, rowIndex) dt.Columns.Cast(Of DataColumn) _
    .Where(Function(col) row(col).ToString() = valueToFind) _
    .Select(Function(col) $“({rowIndex + 1}, {dt.Columns.IndexOf(col) + 1})”)) _
    .ToList()

  5. Use above cell address and increment column index and replace the value with sum stored in SumOfColumns variable

  6. write back the datatable to another excel sheet

you can achieve this using 9-19 lines vba code as well , more efficient and dynamic and less complex.
Try and let me know

4

Can you share vba code

5

@armamidwar are you able to share VBA code?

6

i 'll share the code with you tomm , share me a sample files with few row entries if possible.
amamidwar@comscore.com

7

Summary by Investment Type.xlsx (21.7 KB)

8

@armamidwar I Have shared excel file

1 Like
9
  1. Use Excel Application Scope
  2. Read Range to get all sheet data in DataTable Dt
  3. Get your unique FundCode
    UniqueCodes=dt.AsEnumerable().Select(Function(row) row.Field(Of String)(“Fund Code”)).Distinct().ToArray()
  4. Loop through each FundCode as pass it as argument to VBA code i have shared
  5. Invoke VBA code (code as VBS file)
  6. Save workbook

Mark it as solution if it is you expecting to achieve.

10

So in the for each loop we need to use vba code correct ?

11

yes ,vba code expect a FuncCode

12

Main.xaml (11.5 KB)

Hope this helps
Mark it as solution if helped

13

Showing document is invalid

14

Can you send in zip it would be better?

15

While running invoke vba getting this error

image
image1360Ă—768 92.4 KB

16

hey send me the vba code here and xlsx file

17

Updatesum.txt (1.2 KB)
Summary by Investment Type.xlsx (21.7 KB)

18

let me know once you find solution

19

this is working solution for GA, fix it for other FundCode
test.zip (21.9 KB)

20

Hi it is working for GA not MF while its working for GA as it was filtered with GA that is why it is not working for MF?