How to filter value from excel cell and replace the cells with other value

Hello All,

I am trying to filter a cell value like “Active” or “Leave” then replace the cell value as “not submitted”. Data will be dynamic.
Can any one please help on this

Thanks,
Padmini

1 Like

Fine
hope these steps would help you resolve this
–use excel application scope and pass the file path as input
–inside the scope use a READ RANGE activity and get the output with a variable of type datatable named outdt
–now use a for each row activity and pass the above varaible as input
–inside the loop use a if condition like this
row(“yourcolumnname”).ToString.Equals(“Active”) OR row(“yourcolumnname”).ToString.Equals(“Leave”)
if its true it will goto THEN part of if condition where we can use a assign activity like this
row(“yourcolumnname”) = "no submitted"

then use a write range activity from workbook activities - next to this EXCEL APPLICATION SCOPE and pass the datatable variable outdt as input and sheetname and range as “”
make sure that the ADD HEADERS Property is enabled in the property panel

thats all you are done
hope this would help you
Cheers @chandrashekar_padmin

1 Like

Hello @Palaniyappan,
Main (1).xaml (9.6 KB)
I have created the xaml , but it is not working as expected.
I wanted to write it only to one cell , but it is doing as i want.
can you please check the xaml

1 Like

Sure
hope this would help you
chandra.zip (9.9 KB)

Cheers @chandrashekar_padmin

Kindly let know for any queries or clarification
Cheers @chandrashekar_padmin

1 Like

Hello @Palaniyappan

It is working as expected , thanks much.
Appreciate your quick response :slight_smile:

Also,one more question how can i delete the rows which are not in “not submited” status?

Thanks,
Padmini

1 Like

Great
before write range activty use a FILTER DATATABLE ACTIVITY where in the filter wizard mention the datatable as dt and output datatable as new_dt (a new datatable variable)
inthe columnname mention the columnname within double quotes and in the condition mention as CONTAINS and in the value field mention as “not submitted”
and we got a REMOVE radio button option in the filter wizard (above to the columnname field)

hope this would help you
Cheers @chandrashekar_padmin

1 Like

Hello ,

I have tried it sit ,but it is also keeping the rows with other data.
Can you please check.

Main.xaml (9.1 KB)

1 Like

Fine
is this value correct you mentioned like “not submitted”
image
and also check once with the columnname

Cheers @chandrashekar_padmin

1 Like

Hello @Palaniyappan,

Yes, the value is correct as i have given it in “assign” variable also.
Can you please checkinput.xlsx (8.3 KB) with this input ,it is not giving correct output.

Thanks,
Padmini

Hello @Palaniyappan,

Please let me know if you found something on it?

Thanks,
Padmini

1 Like

Fine let’s try one thing
In the filter condition put as Contains and
In the value be like ”Not Submitted”
And enable the radio button Keep

Cheers @chandrashekar_padmin

@Palaniyappan Even after changing it is not giving desired output.
Please let me know how to proceed further.

Hello ,

Can anyone help on this?

Thanks,
Padmini

1 Like

let me check once
Cheers @chandrashekar_padmin

1 Like

Hello @Palaniyappan

Did u get chance to check this?

Thanks,
Padmini

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