How to filter a column in Excel

[AlexMt]

Hello,

I have an excel file and a column with with some numbers and at the end of the list I have #N/A, like this:

My question is, how do I configure the robot to filter that column to show only #N/A values?

The column name looks like this:

I tried the following methods:

Read Range > Output to Dt

Assign dt to
Dt.Select(“[Avaya_ID_in_Group__Col_E_???] = ‘#N/A’”).CopyToDataTable()
Dt.Select(“[Avaya ID in Group] = ‘#N/A’).CopyToDataTable()
Dt.Select(“Avaya ID in Group (Col E)???” like ‘#N/A’ “).CopyToDataTable()
Dt.Select(”[Avaya_ID_in_Group] = ‘#N/A’).CopyToDataTable()
Dt.Select(”[L3] = ‘#N/A’").CopyToDataTable()

L3 being the cell position on the sheet.

None of the assigned values above worked, most of the time I got the error [“Cannot find column]

Any ideas on how I should fix this please?

Thank you.
Alex

[Vivek_Arunagiri]

Use Filter Table Activity.

Filter Table

[nadim.warsi]

See this similar thread:

Filtering Column in Excel

Hi All, I need to filter column in excel where I want to filter column “courseNumber” which starts with 1013 and save it into another CSV…here is the screenshot of Excel…Quick suggestion and reply would really help. Thanks, Somya [Screenshot%20(147)]

[AlexMt]

Nadim,

I tried that but I get error [“Cannot find column]

[AlexMt]

Vivek,

The file i’m working with is read-only and a table name is not defined for that data. I cannot create a table

[aditya.prakash (Aditya)]

Hey Alex,

you need to Assign Column name

Aditya

[nadim.warsi]

Dt.Select(“[Avaya ID in Group (Col E)???] like ‘#N/A’ “).CopyToDataTable()

[Vivek_Arunagiri]

Please try this.

Dt.select using column index instead of column name

Hi, I am aware of the following method: dt.Select(“[Column Name] =”+“'”+blah+“'” ).CopyToDataTable but I have a dynamic column name so using column index will be a better approach. I got the column name using: cName = dt.Columns(10).ColumnName can someone direct me who to put this variable in dt.select? I tried dt1.Select(credit “> 0”).CopyToDataTable dt1.Select(“credit > 0”).CopyToDataTable They don’t work… Thanks! Lavina Edit: I actually got it! ha - for those who have the same pr…

[AlexMt]

Nadim,

I got the error: the expression contains invalid date contact “#N/A”

[nadim.warsi]

Great

[samir (Samir Mohite)]

Hi @AlexMt,

follow the thread below, it’ll help you.

Filter excel table to remove particular rows

Okay for that take Filter Table activity Excel Application Scope and set the workbook path. Set all the parameters like sheetName, ColumnName and table name. as shown in below shot. [image] for the table name, first you have to create table in excel and pass that table name in activity parameter Set the FilterOptions —> {“S*”} here S is just an example, so it’ll filter table on rows whose values starts with S. I hope this’ll help you. @rishabh.jain

only use FilterOptions as—> {“”}
that’s it