teaeye,
An option that will work is to use Excel's "data extraction" capability. This is found under the menu: Data - Filter - Advanced Filter.
Overall, this component of Excel is relatively easy to use - (and is VERY powerful) - but only AFTER one has overcome a couple of annoying BUGS.
One BUG will inform you it's not possible to extract data to a SEPARATE sheet. This bug surfaces when one attempts to use the menu method. However, by using the VBA method, there is NO problem. And of course the VBA method is preferable because it's faster.
The second BUG can surface when one attempts to place "criteria" (for selecting the data) on a SEPARATE sheet. Ironically, it's "preferable" to place the criteria on a SEPARATE sheet because Excel can "become confused" when the criteria is placed on the SAME sheet. Placing the criteria on a SEPARATE sheet makes it FAR easier to work with the criteria.
Because of these BUGS, and the fact that Microsoft and third parties have not provided sufficient documentation on the use of this VERY POWERFUL feature of Excel, it unfortunately has been avoided by many Excel users.
But BELIEVE me... time spent on learning how to harness this powerful feature will be WELL spent !!!
As a "specific" example of its use related to your described task, here's what you could expect...
You could enter the name "Smith" into a cell, and then click a "macro button" that would cause all the records containing "Smith" to be extracted to a SEPARATE sheet.
Please appreciate that such an example is VERY simple and easy to create. However, if required, this same type of process can be used to extract records based on using increasingly "more specific" criteria. For example, one could extract all the records of people based on each person having the following requirements:
a) Last Name of "Smith", and
b) Having an income of greater than $40,000, and
c) Being over the age of 30, and
d) Being Married, and
e) Having 2 or more children, and
f) Whose children are between the age of 6-12 years, and
g) etc, etc, etc.
Also, you can also use "or" in place of "and".
If you'd like, I can email you a zip file of a variety of example files that should help give you a "jump start", and I could also create an example file for the specific situation you described.
I hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca