Select columns and add repeated values

Hello!
I need help!
I need to take all the users from the “CEDULA” column and the values from the “IBCS” column, but I also need to add the values from the “IBCS” column to which the ID number is repeated and be able to get the “IBCS” of each user and the one that is repeated to get the sum of these.
How can I do this?Prueba 1.xlsx (12.0 KB)

Hi @Beatriz_Eugenia_Duqu

Hi try to do in dictionary method

  1. Read the excel file and store in datatable

  2. Create a dictionary and use for each row to loop thorough each CEDULA row and add a zero value to it

Let’s say d is the dictionary created

d(row(“CEDULA”).ToString) =0, do this process inside the for each row for datatable

Thus all dictionary keys are assigned with zero value

Now use outside for each row use another for each row and inside it use the following assign activity to add the IBCS column value to it

try this inside for each row

d(row(“CEDULA”).ToString)= d(row(“CEDULA”).ToString)+Cdbl(row(“IBCS”))

so finally u will get dictionary with cedulla value as key and total.value for each CEDULA in value section

Hope this idea helps you

Mark it as solution if it helps you

Regards

Nived N :robot:

Happy Automation :slight_smile::slight_smile::slight_smile::slight_smile:

2 Likes

I am still not very clear about the process you indicate.
Excuse me

@Beatriz_Eugenia_Duqu Can you show us the Expected Output for the Input that was provided ? We can understand the scenario in a faster way and suggest a clear answer.

I have some users that are identified in the column cedula, and I have a column of values that is called IBCS, I need my robot to filter these 2 columns, but in the case that the user number of the column cedula is repeated, I need to add the 2 values of the column IBCS that belong to this user.

Hi @Beatriz_Eugenia_Duqu check this workflow Main.xaml (10.6 KB)

The same logic i had explained in workflow with the sample excel file provided by you

Hope it helps

Mark it as solution if you got it

Regards

Nived N
Happy Automation

I get the output with errorSalida

Where the error is occurring?

For each Row, CEDULA is a string

Put it as row(“CEDULA”) in key of dictionary

I put the dictionary key datatype as object not string

Do you mean you need to GROUP BY ‘cedula’ column and calculate SUBTOTAL of ‘ibcs’ column?
The ourtput should be ‘cedula’, ‘sum_ibcs’?

Cheers

Yes, but you only have to add the ICBS column in case the CEDULA column repeats the number

I already made the change but it doesn’t work

But it works fine in my case

Here is the sample workflow
GroupByAndSubtotal.xaml (6.5 KB)

It uses a LINQ expression to group and subtotal

Cheers

image
The same thing happens if I put it in Int

Thanks

How do you change the object type?
I changed it to String and it keeps generating the error
Thanks


I’m running it with my file

@Beatriz_Eugenia_Duqu Can you change the row.field(Of String)(“CEDULA”) to row(“CEDULA”).ToString and check if it is able to give you the output ?