Filter the excel based on 2 conditions

Help StudioX
,
1

Hi everyone, I have an excel containing different columns. i need to delete a row from a excel based on following conditions:

image
image948×387 88.6 KB

  1. Actual State is having 4 different states(ACW, Break, System issues, personal)
  2. each of the states is having its limited time and time is mentioned in time in state column.
  3. for ACW , limited time is 4:00 mins , for break , it is 17:00 mins. system issues= 8:00 mins, personal = 8:00 mins.

i need to first check the these 4 states and its respective time in state values and see if it is crossing the limited time. if any of those states and its respective times are crossed above limit. then i need to remove the entire row and add it on different result.

please refer the above image and please guide me as soon as possible.

thanks

2

Hi I can tell you removing specific row from Excel based on your condition.

Use Excel Application Scope since we will be deleteing row from excel (interacting with excel sheet frequent).

  1. Use a read range==> DT

  2. Intitalize int variable totalRowDeleted=0

  3. Loop over DT to read one by one row and set Index property as index variable (to track index of current row).

  4. Inside your If condition where you want to delete row from Excel:
    Use Delete Range activity,
    In Range: “A”+((index-totalRowDeleted)+3).ToString, where
    index is current row index in DT, and +3 is depend on start row no. in your excel, depends on you have column header at start or you have empty row in start (you can change it based on your case).

  5. Chek the ShiftCells checkbox (should be enabled) and select ShiftOption as EntireRow.

  6. Increase totalRowDeleted by 1, because you have just deleted a row.