How do I Filter Excel COLUMN

Hi,

I’m wondering if there is a way to filter a column in excel, perform whatever actions i need and then unfilter the column?

I am not looking to filter a table but simply a row.

Regards,
Shawn

Hi
Fine
there are two ways we can handle this
–either we can use Filter datatable activity


with which we manage the records to be filtered

–or we can use select method and mention the columns we want to filter based on the values in the row
like this
yourdatatablename = yourdatatablename.Select("[columnname1] = ‘value1’ AND [columnname2] = ‘value2’ "].CopyToDatatable()

hope this would help you
Cheers @shawnmurray

2 Likes

Step 1: Use Excel application scope
Step 2: Read the data from the excel file and store the same to data table
Step 3: Apply the filter into the Datatable
Step 4: Read the Output of the filtered datatable and write the same to excel

2 Likes

Hi @lingamg ,

How would i apply the filter into the Datatable?

Regards,
Shawn

2 Likes

Let us assume your datatable name as Dt_ExcelData.
Use Assign activity.
In the property level you can define like Dt_ExcelData.Select(“Your Excel columnname=Condition”)

2 Likes

hope you have a datatable variable and
pass that as input to the filter datatable activity here and mention the columnname between double quotes and mention the condition and the value to be filtered with, inside double quotes
–so finally we will be getting the filtered datatable with a output datatable option, so create a datatable varaiible in the variable panel with default value as new system.data.datatable
–mention that datatable variable name in the output datatable field
image
image

cheers @shawnmurray

3 Likes

Hi Guys,

Please review:
Main.xaml (5.5 KB) Test.xlsx (9.1 KB)

I’m unsure where i am going wrong but i’m missing something.

Regards,
Shawn

2 Likes

here you go
hope its resolved
shawn.zip (17.5 KB)

Cheers @shawnmurray

2 Likes

Thank you very much, I will have a look.

Cheers @Palaniyappan

2 Likes

No worries
Kindly let know for any queries or clarification
Cheers @shawnmurray

1 Like

Hi @Palaniyappan ,

I want the actual sheet to be filtered and not just the the filtered results to be output if you know what i mean?

Regards,
Shawn

1 Like

so you mean you dont want records with TEST as values right
Cheers @shawnmurray

2 Likes

I mean in the spreadsheet i only want to see the TEST Values
Thanks @Palaniyappan

1 Like

fine
that is what is mentioned in the filter wizard
–i just used output datatable and write line to show you the results
–instead next to the filter datatable activity, remove the output datatable and writeline activities
here you go (Once after running check with the sheet2 in the excel)
shawn.zip (18.7 KB)

its resolved
Cheers @shawnmurray

1 Like

Hi Shawn,

Please find the code. Hope it will help you.

Shawanmurray.zip (18.9 KB)

2 Likes

so did this work @shawnmurray

any issues still to be discussed
Cheers @shawnmurray

1 Like

@Palaniyappan @lingamg
Both those solutions work thank you!

Is there any way to actually use the excel filter option in the application it self without using the recording feature?

Regards,
Shawn

2 Likes

Well if the filter is applied to the file prior to the run
We can get those filtered record alone by enabling a option use filters in read range activity
By that way we can do that
Kindly correct me if I have understood the query wrongly

Cheers @shawnmurray

2 Likes

Fine
If we want to apply the filter in the excel itself we can create a MACRO for it and save that macro in a text file and run that file with a activity called Invoke VBA activity inside a excel application scope
Cheers @shawnmurray

2 Likes

@Palaniyappan
I will try to explain the best i can.
I want to filter the spreadsheet, so example: Column H to only have the word “Test”
So that would update the spreadsheet.
I then run a formula.
Then i filter Column H again back to “All”

1 Like