Excel Automation

Hi Tim,

I’m trying to automate excel data.
I have 4 columns like A,B,C,D
A column has ID’s
B Names
C Numbers
D mail ID’s

I need to add column C data based Column A ID’s’(In column A,same ID will come more than once,we need to add Column C data
Column A Column C
101 10
102 20
103 30
101 40

When we add Column A ID 101 then Column C value will be 50

Please help us

Hi Nikilesh,

I’d recommend to create a JS object with IDs as a key and numbers as a value.
So first of all you need to create a new variable result and assign new Object() value:
Screenshot 2020-09-23 at 17.17.29
Then use For Each Row activity to loop through each row of the table. Don’t forget to set up Template table and name your column variables. I used id and numbers in the following example.

In the loop body use Execute JS activity with following code:

if (result[id] == undefined) { //this checks if there’s a key value pair in the result object.
result[id] = numbers; //if not - it creates it and set’s the value as numbers
} else {
result[id] = result[id] + numbers; //if the key value pair exists - it adds numbers to the value

Screenshot 2020-09-23 at 17.15.35

After iterating through the table you’ll have your result object populated with data.
You can use it like this: result["101"]

Hi Tim ,
Could you please give me detailed solution.


Hi Tim,

Can you please elaborate how you set up Template table.

Did you do it in excel file or in Studio.