Get number of filtered rows in Excel

Use excel as database it will give more flexible way to extract filter data.

Hi ​@Augustin,

or loop trough Excel sheet and incremet counter or write data to table and get the row count from data table.

Cheers

Marc

Hi Marc, thanks for your answer.

I would like to avoid the option of loop and counter or similar solution. (Or keystokes, ctrl+shift+down and copy paste-it to the clipboard to check if it is empty)

>  write data to table and get the row count from data table.

It does not work, the data table will have the number of rows from the unfiltered excel, and the Data Table package has not filtering option AFAIK

Hi Kiran, thanks for your answer.

In that list I should choose Microsoft Access ?

​@Augustin 

here you go…

 

​@Augustin

has this helped solving your problem?

Cheers

Marc

Hi ​@Augustin,

what about this one?

Use “Excel advanced: Find” action for the matching term.

Output is a list variable which you can retrieve the list size which will be same as filtered data rows...

Input file:

 

Filtered one with list size in message box:

Cheers

Marc

Be aware that using Excel as a SQL database has some… well… idiosyncrasies regardless of what application you’re using to communicate with Excel. First, you will need to build your own custom connection string. (See sites like “connectionstrings.com” for assistance.) It is not straight-forward and will require some tweaking for your specific setup, and that the connection string may work on one computer and not another.

Also be aware that the SQL syntax is not 100% of what you would get with a real database like MS-SQL. The commands may have a different syntax and require some changes to work with your connection string.

As long as you set your expectations not to have smooth sailing, you can get it done.

​@Aaron.Gleason thx for this

Thanks a lot for the many different ways of doing it !

In my opinion there still lacks in the package Excel advanced: Get number of rows a box you could tick to select only filtered rows.

I ended up using a DIY solution : 

going to the A1 cell

then go one cell down

then keystrokes ctrl+shift and left-right (this selects all the table)

wait 1 second

ctrl+c

copy from clipboard to a variable

If the value I filter on is not in this variable, over. EDIT : else the number of results I have with find is the number of lines. (can fail if the filter is found in other columns than the table)

It works for me, but it is probably not the best solution