Total up several cells' values of certain columns

Help
,
1

A csv existing of 6 columns (Delimiter: Semicolon) has to be re-arranged by consolidating rows with the same ID.
So I first need to identify all different IDs so that afterwards rows with the same ID can be Consolidated within a single row. Three of the related values (in the other columns) are equal for each group (of identical IDs). So either the first or last record can be taken.
But for two of them (marked blue) I need to total up the values in the cell (related to the same ID) - see screenshot below:

image

Please find a sample file attached…
UiPath Forum.zip (442 Bytes)

To sum it up…
Columns D and E need to be consolidated based on the (same) ID in column A.
For the other columns either the first or the last record can be taken.
Thanks in advance for your help! :pray:

2

Hi @MarcJo,

  1. Read the data from the Excel sheet and store it a Data Table.
  2. Use the GroupBy and Select Method to achieve the logic and store the result as a new Data Table

Ref: DataTable.Select Method (System.Data) | Microsoft Learn

3

Hi @ranjith_udayakumar,
the first part (reading data from excel and store it in Data Table) I already did of Course.
Can you please be more specific with the second part since I am not so much into VB.net Methods.
Any concrete idea about the final Code?
Thanks in advance

4

1st step to filter the distinct value based on Policenumm

DataTable dtdistinct=DataTableMain.DefaultView.ToTable(true,“Policenumm”)

  1. Loop the above dtdistinct and get the sum

sample :-
(from line in DataTableMain.select where line(“Policenumm”).equals(“DistinctValue”) select cint(sum(line(“Columname”)))

5

Marco, If you find it time-consuming. Kindly try the following package which has an activity for your problem.

6

Hi @ranjith_udayakumar,
thanks for your advice.
I installed the package you suggested and tried to find a solution by using activity “Group by Aggregation”.
But honestly I do not know how to configure it in detail in order to meet my requirements.
Can you help me out please?
Thanks in Advance :slight_smile:

7

Hi @karthick,
thanks for your help.
I managed to realise the first part.
So I got a list of all distinct values of a specific column ("Vertrags-OM) - see the screenshot below where the actual file (marked yellow) and the “filtered” file (marked red) are compared:

image
image472×578 16.3 KB

But how exactly do I have to proceed in order to get the sum of another column’s values based on the ID in column “Vertrags-OM”?
For the example showed in the screenshot I need to have 11 rows (+header) in the end (each having the ID’s sum of specific columns)
Thanks in Advance for your support :slight_smile:

8

@MarcJo, Yeah, I can help you out. where are you facing issues?

9

using step 2 of my previous post will get you the sum of column value.

10

@ranjith_udayakumar,
I never worked with the Group by Aggregation activity and I am not sure what Settings I have to choose in order to:

  1. get all distinct IDs of column “Vertrags-OM” (with the help of @Karthik I actually found another way to realise this - see my post before)
  2. Based on this I need to sum up all values of two different columns belonging to the same ID so that I have one row for each ID with the total of the associated values
  3. for the other values (mapped to further columns) of this row the first value of all the required rows should be taken

I hope the following Picture makes clear what I try to achieve…

image
image988×357 21.6 KB

Cheers,
Marc

11

@karthick,
for now I implemented it like this…

image
image1325×695 24.1 KB

you said this is the next step…
(from line in DataTableMain.select where line(“Policenumm”).equals(“DistinctValue”) select cint(sum(line(“Columname”)))
But can you please be a bit more precise?
Thanks in Advance :slight_smile:

12

using Assign activity and use the below query to get the sum
(from line in DataTableMain.select where line(“Policenumm”).equals(“DistinctValue”) select cint(sum(line(“Provision”)))

We have two value in Provision . Which value should be considered?

13

@karthick,
it says that sum is not declared or invalid in the current range.
What does line stand for?
There are two columns which need to be calculated - “Provision” and “Berechnungsgrundlage”

14

Hello @MarcJo
You can easily do this using LinQ

Check this workflow out
Marcoj Solution.xaml (11.9 KB)

15

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