Replacing one row with more than one row

Hello Everyone
I want to replace one row from an excel file from more than one row from another excel file based on certain criteria.
Please help me out


Read the first excel and Iterate it using For Each Row
Using Build Datatable (dbop)create a Datatable same as the output header.
Use If condition to check the criteria
If Exist , using Add data row add the replacing data to the dbop Datatable
Else, add the original data to the Datatable.
Then write the output.
hope this will help you, revert back for any help

Suresh J

@sureshj Thanks…firstly, I want to replace one row with more than one row based on a condition. So, I need to insert rows in excel but that should based on a certain criteria. How to do that?


Using IF Activity the condition or criteria is true that is the row you want to replace enter then add the number of row to that Datatable (dbop).
If False add the same row to the Datatable (dbop).

I have attached a sample go through it, revert back if help required.
replace.xaml (12.9 KB)
Suresh J

I am using this statement. What to write in select format?


Sorry, I can’t understand which Format?
Could you please elaborate.

Suresh J

What should I write in place of select format in the below statement from the workflow that you gave

  • dataTable.Select(String.Format(selectFormat,keyword)).Length

Actually I want to count the no of occurance of an element in a row of an excel and based on the no of occurance, I want to create the no of rows in another excel.
Can you help me out with this ?

If it is 2 excels and done entirely within excel then this should probably be written in VBA instead. It is created for excel specifically and has many more excel-specific classes, methods, properties, etc available to be used. This would much simpler than using UiPath to achieve the same thing.

If you do go the UiPath route, you have to use 2 different datatables: the first one you iterate through, and the second one is the one you update. You have to use 2 because you can’t alter a table while you are iterating through it. It will be overall much more confusing than just writing it in VBA and using the ‘Execute macro’ activity instead

@Dave Thanks
Do you know how to count the presence of an element in an excel sheet? Actually, I need to count the presence of an element in an excel and then add that much rows in another excel. Please help me out.

I’m not entirely sure what you mean by “element” in this context. Do you mean a UiElement? If so, then if there are multiple of the same UiElement it will likely have an ‘idx’ attribute. The highest ‘idx’ number will be the count of that UiElement.

If by “element” you mean specific value/string within a cell in excel, then that would be done in a completely different way and more info would be needed. How is the worksheet structured? Is it already setup in a datatable format or does it need to be altered? Is it just looking for the specific value in a single column or the entire worksheet? Does it need to look on a single worksheet or the entire workbook?

I am talking about specific value within a cell in excel. I have two excel file. In 1st excel file, I have two columns in excel -

  1. Date
  2. Marks
    I have to match these two columns with 2nd excel. If they match, I need to count the number of occurrence of same marks in 2nd excel i.e. if I have 12 in 1st row and 12 in 2nd row, then the count is 2 and then I need to make that much rows in 1st excel.
    How to do that?