Excel Operations logic help

[DipinDevP]

Hi,

I am requesting a logic help here which I need to handle with Excel VBO in Blue Prism. It would be great, if  someone can share ideas on how to implement it.

Please check the attached Excel file Images, in which there are 2 sets of data with ID 111 and ID 222.

Logics to implement:

Logic 1 - ID 111:

If the Status is ‘Waiting’ and Group is ‘Phone Connect’, find Sum of Time from the next row to the row which Status turns ‘Active’ and set the value in Required Time Column in the last row of that ID.

Here there are 2 groups matching above conditions. D7 to D8 and D12 to D15.

(Sometimes more than 2 groups may be there).

So, we need to take sum of both sums (D7 to D8 and D12 to D15) and set in E20.

Logic 2 - ID 222:

If Status is ‘Waiting’ even before Group turns ‘Phone Connect’, find Sum of Time from the same row (with Group ‘Phone Connect’) to the row which Status turns ‘Active’ and set the value in Required Time Column in the last row of that ID.

Here there are 2 groups matching above conditions. D29 to D31 and D34 to D38 (Sometimes more than 2 groups may be there.)

So, we need to take sum of both sums (D29 to D31 and D34 to D38) and set in E42.

In single Excel we may have set of data with more than 1000 different IDs. Here I put just 2 ID setss 111 and 222 as examples.

Can anyone suggest how we can implement above logic using MS Excel VBO or any Extended VBOs?

Thanks in advance,

Dipin Dev P