How to creat a list from a DT and fill it and remove duplicates and sum the amounts

Hello everybody,

I’m working on a project where I need to filter a DT on 3 items and sum the amounts for each items ("Pôle).

I have this DataTable (out_dt_Strat), with 5 colunms, I need to filter for each “Pôle”, and sum all the amount (col Montant HT).

I’ve started to put the column “Pôle” into a List to have the list of each Pole (just once). Like this, if the client wants to add a new Pole, I can have it automatically.
For the moment, I can collect each “Pôle” in a list. see below

But then, I don’t know how to sum all the amount for each “Pôle”.

Coz at the end, I need to have the result for each “Pôle” in 3 variables
A = sum Montant HT of France, Belgium
B = sum Montant HT of America, Algeria, Austria
C = etc…

But I am stuck, I don’t know how to proceed. I’ve tryied with a formula dt.compute(Sum(colname) but it’s not working (or i’m using it in a wrong way)

Thank you in advance for your help

Again @ppr maybe you can help me out. :blush:

Kind regards


Hi @najoua.abbaci

U can try this way

Before the for each loop u can intialise the dictionary

d= New Dictionary (Of String, Double)

Since u are looping through each element of list

Then inside the for each loop do the following:

  1. Filter the datatable out_dt_Strat with item
    using filter datatable activity and put the result of filter in dt3

  2. Then use the below assign query

sum= dt3.AsEnumerable().Sum(Function (row) CDbl(row(“Montant HT”)))

Where sum is double varible datatype.

Now to store the data u can dictionary concept here with key as item and value as sum

d(item)= sum

So in this way u can store the data

Hope it helps you

Mark it as solution if this resolves ur query


Nived N :robot:

Happy Automation :relaxed::relaxed:


Thank you @NIVED_NAMBIAR for your answer.
I tryied but it’s not working. I have a global sum (and i didn’t check if it is the sum of all the amount).
But I need a sum for each “Pôle”. But i’m trying to fix this. For the moment, not succeeded.

Kind regards

Can u share the sample excel file ?

yes sure
here is a sample of the excel file
STRAT_Clean.xlsx (8.6 KB)

Hi @najoua.abbaci
Check this workflow
I had made the dictionary format that look finally like this


By use of the dictionary you can be able to retrieve the values very easily by using key (602.9 KB)

Mark it as solution if it resolves your query
Nived N
Happy Automation

Thank you very much @NIVED_NAMBIAR it seems to be working

I have one last (maybe simple) question, but how can i use B, F and L with their value, coz I need this values (B = 102075) to write it into the client software.

lets say u create an array of string


Now use for each activity to loop through each element in array
(for each item in array_1)

then inside it use the following:
d1(item) to obtain the value and use some ui actions to write that one to a client software

Nived N

1 Like

ok thank you @NIVED_NAMBIAR

I have an error message, when I lauch with all my data, I have : “Assign: Conversion from string “” to type ‘Double’ is not valid.”
For the assign sum = dt1.AsEnumerable().Where(Function(row1) row1(“Pôle”).ToString.Equals(row(“Pôle”).ToString)).Sum(Function(row1) CDbl(row1(“Montant HT”)))

Can u show the excel file

I think there are some missing data in the Montant HT column

This is the excel fil with all the Montant HT
I just had to change the name of societies (for the client)STRAT_Clean.xlsx (10.4 KB)

I think it was because in the Montant HT their was an empty row. I removed it and it was working without this error message

Hi @najoua.abbaci
I think it is working fine for me
see the output i got

Nived N
Happy Automation

1 Like

Yes, for me too. Like I said I had an empty field but since i removed it, it is ok !
Thank you very much for your help ! @NIVED_NAMBIAR


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