How do I Filter Excel COLUMN

Help
1

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

2

Hi
Fine
there are two ways we can handle this
–either we can use Filter datatable activity
https://activities.uipath.com/docs/filter-data-table
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
3

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
4

Hi @lingamg ,

How would i apply the filter into the Datatable?

Regards,
Shawn

2 Likes
5

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
6

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
7

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
8

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

Cheers @shawnmurray

2 Likes
9

Thank you very much, I will have a look.

Cheers @Palaniyappan

2 Likes
10

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

1 Like
11

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
12

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

2 Likes
13

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

1 Like
14

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
15

Hi Shawn,

Please find the code. Hope it will help you.

Shawanmurray.zip (18.9 KB)

2 Likes
16

so did this work @shawnmurray

any issues still to be discussed
Cheers @shawnmurray

1 Like
17

@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
18

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
19

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
20

@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