Hi rohitsle,
There are multiple options to do this. You can choose an approach depending on what fits your situation based on how large the excel is, or whether you have more control on the Excel or on the Collection, etc.
i. You can extract the excel sheet into a collection. Then loop through the collection (lets call it Input Collection) where you compare the current row value with its previous row. You can use Date functions here.
As you navigate through each row, copy the same row into another collection (say Output Collection) based on the below conditions. You can use the Copy Row actions of the Collection VBO. Also take a look at the Append Collection action and use the one(s) that are feasible for this situation.
a. If there is a difference of 1 hr -> then it means it is correct -> Copy row to Output Collection -> Continue looping on Input Collection
b. If there is a difference of more than 1 hr -> then it means it is time to insert the missing row(s) -> insert a row to Output Collection by adding 1 hr to the previous row. Continue this till the time there is a gap of 1 hr between the current row of the Input Collection and the row that you just inserted into the Output Collection -> Once the timings are correct (meaning once you have added the yellow rows for one set) continue the loop on the Input Collection. Then the next yellow set and so on.
Handle the above logic appropriately for the 1st row in the collection while finding the 1 hr difference between current row and previous row as there will be no previous row.
Add 0 to the 'time in sec' column of the Output Collection for Step b.
When the loop on the Input Collection is over, the Output Collection will have the correct rows as per your requirement.
Its now up to you what you do with this collection. You can clear the old data and overwrite this Output collection onto the sheet.
ii. Another option is to make the changes directly on the excel sheet itself.
Use the same logic as No i. for comparing the data in the current row and the previous row, but this will involve a Code stage to loop through the data in the Excel sheet itself. Insert a row directly in the Excel sheet and update the data as needed.
iii. This approach will be without a Code stage but could be slower since you are reading once cell at a time.
Use the in built action Get cell value of the Excel VBO and read the its content.
Use a Counter data item and use it in the Get cell value; eg; "A & [Counter]" will give values of A1, A2, A3 as you increment the Counter using a Calc stage.
Use the same comparison logic as explained in approach no. i. above.
When its time to insert a row refer the Post #7 here
http://rpaforum.net/threads/how-to-insert-row-in-excel-sheet-using-ms-excel-vbo.7589/#post-13006
Make sure to handle the Counter accordingly when you add a new row.
Continue the Get cell value till you get a blank value indicating that you have reached one cell after the last row of the sheet.
