Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
Hi Robert
You could just use a filter. If you are using XL2003, place cursor within data listDataListCreateclick my list has headers If you are using XL2007, place cursor within data listInsert tabTableclick 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" wrote in message ... 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 __________ 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
I'd use the built-in data|filter|autofilter.
Robert Crandal wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
On Feb 25, 3:25*am, "Robert Crandal" wrote:
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 fastest would be a filter. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options (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 "Dave Peterson" wrote in message ... I'd use the built-in data|filter|autofilter. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options (filter)
Nope.
But in xl2003 menus, you can use: Data|filter|show all data to start afresh. Robert Crandal wrote: 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 "Dave Peterson" wrote in message ... I'd use the built-in data|filter|autofilter. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
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! "Roger Govier" wrote in message ... Hi Robert You could just use a filter. If you are using XL2003, place cursor within data listDataListCreateclick my list has headers If you are using XL2007, place cursor within data listInsert tabTableclick 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 -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
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 DataFilterShow 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 Robert Crandal wrote: 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! "Roger Govier" wrote in message ... Hi Robert You could just use a filter. If you are using XL2003, place cursor within data listDataListCreateclick my list has headers If you are using XL2007, place cursor within data listInsert tabTableclick 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 -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
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 DataFilterShow 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 Robert Crandal wrote: 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! "Roger Govier" wrote in message ... Hi Robert You could just use a filter. If you are using XL2003, place cursor within data listDataListCreateclick my list has headers If you are using XL2007, place cursor within data listInsert tabTableclick 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 -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
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 DataFilterShow 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 Robert Crandal wrote: 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! "Roger Govier" wrote in message ... Hi Robert You could just use a filter. If you are using XL2003, place cursor within data listDataListCreateclick my list has headers If you are using XL2007, place cursor within data listInsert tabTableclick 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 -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search options
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 DataFilterShow 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 Robert Crandal wrote: 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! "Roger Govier" wrote in message ... Hi Robert You could just use a filter. If you are using XL2003, place cursor within data listDataListCreateclick my list has headers If you are using XL2007, place cursor within data listInsert tabTableclick 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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tools options view window options | Excel Discussion (Misc queries) | |||
Send mail with outlook message options-Delivery OptionS | Excel Programming | |||
Default Search Options | New Users to Excel | |||
Working with options from within Tools Options clears the Clipboar | Excel Programming | |||
How to diasble the 'Tools - Options - View - Comments' options? | Excel Programming |