Excel automation problem

Hi All,

We have data in excel, img for reference

i want to read total no of P for Rakesh and Purvi and write in another sheet and also want to calculate total no of leaves (L+H) for both, img for reference

please help with the logic.

Hi @Rakesh_Tiwari,

This is something I believe @prasath17 can help.


1 Like

Hi @Rakesh_Tiwari,

I am sending you a short code that I think may help you.
BlankProcess9.zip (37.3 KB)



Thanks for you reply.

i tried myself and tried to write in another sheet, but what i am getting is find in below

it is adding extra two column and also not calculating correctly(Total_P, Total_LH)

Main.xaml (12.4 KB)
file attched


You could also work with excel formulas here and type wherever you want to:

=COUNTIF(A2:D2, “P”)
=COUNTIF(A2:D2, “<>P”)


1 Like

Does anyone know a shorter way to do this? I know for searching values in column, we can use dtTestDataTable.Select("[ColumnName]='Value'").Count. Is there something similar like row(1)='Value'?

Hi @asgRPA ,

i am not aware of excel formula, kindly suggest some other idea.

Hi @Rakesh_Tiwari ,

You should just remove the activities where you add columns if the columns are already created when you read the excel. And inside the for each row you need to add the update row item activity to populate the columns.

As per suggested i removed the activity, but getting below error.


Hi @Gresilda_Balla ,

When i am using column number it running well, but changed the column name, see the result below.

Any reason?

Hi @Rakesh_Tiwari
When you read the datatable from excel Set as true the addHeaders option. And do the same when you Write it or, set the AddHeaders option as false and start writting from A2 cell.


i kept addheader option checked while reading and does same while writting it, but getting same error actually issue with column name, bot is not able to read it properly.

Try this code. Without changing anything just run it first.
BlankProcess9.zip (50.3 KB)

1 Like

no o/p, i ran it by changing the folder location that’s it.

But no output

Did you also change the file location at Write Range?



can you check my code and let me know where am doing wrong.BlankProcess9.zip (37.3 KB)