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

[DipinDevP]

I missed to add column references in the above post. 

Column A : ID

Column B : Group

Column C : Status

Column D : Time

Column E : Required Time

[harish.mogulluri]

HI @DipinDevP

Logic 1 - ID 111:

Read the data in to collection : Coll1_Logic1
Create new empty collection:  TempColl_sum_logic1( By adding Status, Group and Time column this is optional you can do with out using this collection)
Flag: Group_Status(Match): initialize as false

Loop the collection and

a)   write condition some thing like this ("Column B : Group" And "Column C : Status" ) or flag Group_status(Match) is true
If both  the condition matches  as per your requirment which is ( Status is ‘Waiting’ and Group is ‘Phone Connect’)  and  use the calc stage  to update the flag some thing like Flag_Status_Group( true)
and

b) add new rows in to the TempColl_sum collection and pass all the status, Group and time values in to this collection  > perform this step until your second condition met  which is status is Active and Group is Phone connector
c) once the loop reaches to the second condition* status is Active and Group is phone connect) . then loop  the second collection and sum all the values in the time column and place this value in some temp data type
You can update the Group _status flag as false after the  completion of loop in the second collection

Note: TempColl_sum_logic1 is  optional you can do with  out using this one. there might be different or better approach as well.

You can perform similar logic in excel VB script or code stage in blue prism

You can apply above logic if needed some tweaking iyou can perform Logic 2 - ID 222 as well.

[DipinDevP]

@harish.mogulluri Thanks for your great help!

I will definitely try your suggestions.