Check if Column Contains Specific Value

Hello Team,

How can we update column Final_State based on another Column

Example:
If Portal_Status Contains (Project 682383 completed )
Then
Column Final_State would be updated as completed

And If
Portal_Status Contains (Project 3828282 , data updated)
Then
Column Final_State would be updated as Not completed

Thanks in advance team

Have attached Reference Excel File
HR Data.xlsx (6.7 KB)

1)Sheet1 Contains Reference

2)FinalFormat is the Format that we should have as output

Can we update without using loop concept

Hi @NISHITHA

Can you try below code

Code:

For Each row As DataRow In DT.Rows
    Dim portalStatus As String = row("Portal_Status").ToString()

    If portalStatus.Contains("completed") Then
        row("Final_State") = "Completed"
    Else
        row("Final_State") = "Not Completed"
    End If
Next

HR Data.xlsx (9.9 KB)

Regards,

  1. Read Range: Excel File (Output as Datatable)
  2. For Each Row activity:

Assign IF activity within the loop:
IF activity (condition based on Portal_Status):
Condition: Currentrow(“Portal_Status”).ToString.Contains(“Project 682383 completed”)
Then: - To: Currentrow(“Final_State”) - Value: “completed”
Else:
Condition: Currentrow(“Portal_Status”).ToString.Contains(“Project 3828282 , data updated”)
Then: To: row(“Final_State”) - Value: “Not completed”
3. Write Range

Happy Automation :slight_smile:

Thanks @Nawazish_Ahmad for the Reference

Can we add * for Project No

Example:
Currentrow(“Portal_Status”).ToString.Contains(“Project * completed”)

It would be different Project No’s that are present how could we accomodate it

Thanks @lrtetala for the Logical approach

@NISHITHA

Hope you got the solution for your question if yes please mark it as solution to close the loop and it will be helpful for others.

Cheers!!

@lrtetala one question

Can we add * for Project No

Example:
Currentrow(“Portal_Status”).ToString.Contains(“Project * completed”)

It would be different Project No’s that are present how could we accomodate it

@NISHITHA

The above code will work for any project number no need to add any asterisk it will see the word Completed if it is in that cell it writes as Completed.

Cheers!!

@NISHITHA

Just use only
Currentrow(“Portal_Status”).ToString.Contains(“completed”)
Or
Currentrow(“Portal_Status”).ToString.Contains(“data updated”)

Happy Automation :slight_smile:

Thanks @lrtetala and @Nawazish_Ahmad

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.