Need to filter excel column

[Mukthar_Valli]

Hi Team,

Need to filter below excel column

if Current status column contain No Service and New service lunch contains bilateral or inbound then action need from assign activity like “Add”

same for not=no service also as per screenshot

same for bilateral/inbound/outbound also

anyone help please

thanks
shaik

[mkankatala (Mahesh Kankatala)]

Hi @Mukthar_Valli

Your query is quite confusing, could you be more specific and do you want to store which value in a list or string

[Darshan_Sable]

@Mukthar_Valli you can create a excel formula for action column

=IF(OR(AND(A2="No Service", OR(B2="bilateral", B2="inbound")), AND(A2="not=no service", OR(B2="bilateral", B2="inbound")), AND(OR(A2="Bilateral", A2="Inbound", A2="Outbound"), B2="outbound")), IF(A2="No Service", "Addition", IF(A2="not=no service", "Modification", "Deletion")), "")

where A2 is current status column and B2 is New Service Launch column

output can be as below

Current status	New service launch	New Status	Action
No Service	bilateral		Addition
No Service	inbound		Addition
not=no service	bilateral		Modification
not=no service	inbound		Modification
Bilateral	outbound	unilateral	Deletion
Inbound	outbound	unilateral	Deletion
Outbound	outbound	unilateral	Deletion

[Mukthar_Valli]

@Darshan_Sable Output is correct

Can you provide Sample workflow please?

Thanks
Shaik

[Darshan_Sable]

@Mukthar_Valli

1. Read the Excel Data:
Use the Excel Application Scope activity to open your Excel file.
Use the Read Range activity to read the data into a DataTable.
2. Iterate Through Each Row:
Use the For Each Row activity to loop through each row of the DataTable.
3. Calculate Row Index:
Use an Assign activity to calculate the row index.

rowIndex = DataTable.Rows.IndexOf(currentRow) + 2

This adds 2 to the row index to match Excel’s row numbering.

4. Write Formula to Cells:

Use the Write Cell activity to write the formula into the specific cells.

Write Cell: =IF(OR(AND(A" + rowIndex.ToString + "=\"No Service\", OR(B" + rowIndex.ToString + "=\"bilateral\", B" + rowIndex.ToString + "=\"inbound\")), AND(A" + rowIndex.ToString + "=\"not=no service\", OR(B" + rowIndex.ToString + "=\"bilateral\", B" + rowIndex.ToString + "=\"inbound\")), AND(OR(A" + rowIndex.ToString + "=\"Bilateral\", A" + rowIndex.ToString + "=\"Inbound\", A" + rowIndex.ToString + "=\"Outbound\"), B" + rowIndex.ToString + "=\"outbound\")), IF(A" + rowIndex.ToString + "=\"No Service\", \"Addition\", IF(A" + rowIndex.ToString + "=\"not=no service\", \"Modification\", \"Deletion\")), \"\")

let me know if further help needed

[lrtetala (Lakshman Reddy)]

Hi @Mukthar_Valli

Can you try this

Invoke Code:

For Each row As DataRow In dt.Rows
   If row("Current status").ToString.Contains("no service") AndAlso row("New service launch").ToString.Contains("inbound/bilateral") Then
	   row("Action") = "Addition"
   End If
    If row("Current status").ToString.Contains("not= no service") AndAlso row("New service launch").ToString.Contains("inbound/bilateral") Then
	   row("Action") = "Modification"
   End If
       If row("Current status").ToString.Contains("Bilateral/Inbound/outbound") AndAlso row("New service launch").ToString.Contains("outbound/unilateral") Then
	   row("Action") = "Deletion"
   End If
Next

Regards,