Search options

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

Robert Crandal

My spreadsheet contains about 100 to 200 rows of data.
We will also add more rows of data over time. If I wanted
to add a search feature which would display a subset of
rows that match a specific criteria, what is a good way to
do this??

Should I just create a loop that steps through each row
and picks out all the matches and displays them on
a userform or something??? Are there other ways???

thank you
 
Hi Robert

You could just use a filter.
If you are using XL2003, place cursor within data
list>Data>List>Create>click my list has headers

If you are using XL2007, place cursor within data list>Insert
tab>Table>click my table has headers

In both cases a dynamic List/Table will be created, which will grow as you
add more items.
Using the dropdown on each column header you can select individual values,
or begins with, or contains and many other options.

If you don't want to use the dropdowns, I have created a file called
FastFilter, which just allows you to type your search criteria directly into
the row above your filter.
It can be downloaded from
http://www.contextures.com/Fastfilter.zip
--
-------
Regards
Roger Govier

Robert Crandal said:
My spreadsheet contains about 100 to 200 rows of data.
We will also add more rows of data over time. If I wanted
to add a search feature which would display a subset of
rows that match a specific criteria, what is a good way to
do this??

Should I just create a loop that steps through each row
and picks out all the matches and displays them on
a userform or something??? Are there other ways???

thank you



__________ Information from ESET Smart Security, version of virus
signature database 4893 (20100224) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4893 (20100224) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4901 (20100227) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
I'd use the built-in data|filter|autofilter.

Robert said:
My spreadsheet contains about 100 to 200 rows of data.
We will also add more rows of data over time. If I wanted
to add a search feature which would display a subset of
rows that match a specific criteria, what is a good way to
do this??

Should I just create a loop that steps through each row
and picks out all the matches and displays them on
a userform or something??? Are there other ways???

thank you
Click to expand...
 
My spreadsheet contains about 100 to 200 rows of data.
We will also add more rows of data over time.  If I wanted
to add a search feature which would display a subset of
rows that match a specific criteria, what is a good way to
do this??

Should I just create a loop that steps through each row
and picks out all the matches and displays them on
a userform or something???   Are there other ways???

thank you
Click to expand...

fastest would be a filter.
 
I don't have much experience using that, but I will try.
Do you know if the autofilter removes the non-matching
rows?? I am not interested in deleting any rows of data,
I just want a visual of all matches.

Thank u Dave
 
Nope.

But in xl2003 menus, you can use:
Data|filter|show all data
to start afresh.
 
Hi Roger.....thank you for responding.

We will be adding new rows of data to this spreadsheet
every day. The most recent entry will be placed at the top
of the table to indicate it is the most recent entry. The thing
that I don't like about the Auto Filter feature is that it has
options to sort or re-arrange the entire table, which might
be unacceptable for my needs. I do not want anyone to
have the ability to re-arrange the rows of data under any
circumstances at all. Most of my users are not proficient
with the built in auto filter, so I was looking for something
more intuitive.

BTW, if I do an auto filter or a search using Exce's Auto
Filter feature, I notice that my entire table disappears
except for the target rows. How do I make the entire data
re-appear like normal?? I always get the impression that
my data disappears or gets destroyed during a filter or
sort operation.

BTW, does your FastFilter file simply call macros that
run Excel's auto filter functions?? I might look into that,
I just wanted more info before I test it.

thank you!
 
Hi Robert

Firstly, if you have no data showing, then there were no rows that
matched the criteria set. You can either click the dropdown arrow again
and select All, or Data>Filter>Show All.

When you use Lists, then the Sort option will always sort the whole of
the list by the column selected, and will not just sort the single
column, which would get all of the data out of alignment. If your data
is in reverse date order, then it would be quite easy to get it back
into that order.

Yes, my FastFilter does use VBA code to set the criteria on the relevant
columns, using Autofilter. The code as it exists, leaves the dropdown
arrows visible, and therefore accessible to users, but it can easily be
modified to hide all of the filter arrows.

If you need the modified version, mail me at
roger at technology4u dot co dot uk
Change the at and dot to make a valid email address

I will then send you a modified version.

Regards
Roger Govier
 
Hi Robert

Firstly, if you have no data showing, then there were no rows that
matched the criteria set. You can either click the dropdown arrow again
and select All, or Data>Filter>Show All.

When you use Lists, then the Sort option will always sort the whole of
the list by the column selected, and will not just sort the single
column, which would get all of the data out of alignment. If your data
is in reverse date order, then it would be quite easy to get it back
into that order.

Yes, my FastFilter does use VBA code to set the criteria on the relevant
columns, using Autofilter. The code as it exists, leaves the dropdown
arrows visible, and therefore accessible to users, but it can easily be
modified to hide all of the filter arrows.

If you need the modified version, mail me at
roger at technology4u dot co dot uk
Change the at and dot to make a valid email address

I will then send you a modified version.

Regards
Roger Govier
 
Hi Robert

Firstly, if you have no data showing, then there were no rows that
matched the criteria set. You can either click the dropdown arrow again
and select All, or Data>Filter>Show All.

When you use Lists, then the Sort option will always sort the whole of
the list by the column selected, and will not just sort the single
column, which would get all of the data out of alignment. If your data
is in reverse date order, then it would be quite easy to get it back
into that order.

Yes, my FastFilter does use VBA code to set the criteria on the relevant
columns, using Autofilter. The code as it exists, leaves the dropdown
arrows visible, and therefore accessible to users, but it can easily be
modified to hide all of the filter arrows.

If you need the modified version, mail me at
roger at technology4u dot co dot uk
Change the at and dot to make a valid email address

I will then send you a modified version.

Regards
Roger Govier
 
Hi Robert

Firstly, if you have no data showing, then there were no rows that
matched the criteria set. You can either click the dropdown arrow again
and select All, or Data>Filter>Show All.

When you use Lists, then the Sort option will always sort the whole of
the list by the column selected, and will not just sort the single
column, which would get all of the data out of alignment. If your data
is in reverse date order, then it would be quite easy to get it back
into that order.

Yes, my FastFilter does use VBA code to set the criteria on the relevant
columns, using Autofilter. The code as it exists, leaves the dropdown
arrows visible, and therefore accessible to users, but it can easily be
modified to hide all of the filter arrows.

If you need the modified version, mail me at
roger at technology4u dot co dot uk
Change the at and dot to make a valid email address

I will then send you a modified version.

Regards
Roger Govier
 
Back
Top