Hi everyone,
I am trying to get the number of filtered rows in Excel Advanced, is there an easy way to do so ?
It seems to me that the action “Get number of rows” will return the number of unfiltered rows.
Hi everyone,
I am trying to get the number of filtered rows in Excel Advanced, is there an easy way to do so ?
It seems to me that the action “Get number of rows” will return the number of unfiltered rows.
Use excel as database it will give more flexible way to extract filter data.
Hi
or loop trough Excel sheet and incremet counter or write data to table and get the row count from data table.
Cheers
Marc
Hi
or loop trough Excel sheet and incremet counter
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
Use excel as database it will give more flexible way to extract filter data.
Hi Kiran, thanks for your answer.
In that list I should choose Microsoft Access ?
here you go…
has this helped solving your problem?
Cheers
Marc
Hi
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.
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
Hi
Sorry... but you asked for "Get numbers of rows" which is an integer of rows like (8) rows.
What you describe now is the content of the filtered rows which is not the number of rows.
Don't be confused with the meaning of "get number of rows".
Cheers
Marc
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.