Excel automation based on condition

Hi All,

I am trying to automate an excel file which contains some data and based on the some condition(Description= uber) it should calculate the 10% of that amount and write in the specific cell. Amountcal.xlsx (9.6 KB)

kindly help me to automate this.

  1. Use read range activity to read the table from excel. The output will be a datatable.
  2. use for each row activity to loop through each items in datatable.
  3. use if condition and check row(“Description”).Tostring.ToLower.Contains(“uber”). The result will be a boolean value.
  4. If true, update 10% of amount column. otherwise move to next row.

Let me know if it doesn’t work.

Hi @Rakesh_Tiwari Check this…Sequence11.xaml (7.3 KB)

Hi Tushar,

Thanks for replying.

Here we know the range so it is easy to define the range.but if suppose there is lot of data in it,then how to write the logic.

.NewFlowChart.xaml (9.5 KB)
i have written this code, kindly let me know how to write in a that manner.

If the data is huge, then write a macro in the excel to compare the data and update the 10% value.
Use execute macro activity to run the process.

Hi Madhavi.

Thanks for replying.

can u guide me with the logic as i stuck in middle.

here is my code.
NewFlowChart.xaml (9.5 KB)

There is one limitation if you want to write macro. The template of the excel should remain same always. i.e., Macro should be saved in the excel and in the same excel data has to be put every time.

How you are receiving the AmountCal data? Customer sending this or you are generating this by reading data from applications?

customer is sending the data

Is it possible to send the excel template to customer and ask him to update the data in that template every time?

I have written two approaches.
Un-comment the Options and check the flow.
For huge files, Macro is recommended. I haven’t done that approach here. That needs time.
NewFlowChart.xaml (17.5 KB)

Hi Madhavi,

ok, thanks it worked.

1 Like

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