Try using MS Query.
If you understand SQL, then you can use MS Query to write a parameter query that lets you focus in on a particular name and that also allows you to select which columns will be displayed.
For example, I made a test workbook called QueryTest. On a worksheet called Data I have the following data:
Surname First_Name Age City
Smith Adam 46 Toronto
Jones Shirley 32 Los Angeles
Smith Warren 71 San Diego
Green Dallas 27 Dallas
Smith Zulu 13 Pittsburgh
Then on a second worksheet called Query, I created a query starting on row 3 by using /Data/Get External Data/New Database Query.
The SQL itself looks like this:
SELECT `Data$`.Surname, `Data$`.`First_Name`, `Data$`.City
FROM `C:\Documents and Settings\My Documents\QueryTest
WHERE (`Data$`.Surname=?)
ORDER BY `Data$`.`First Name`
By leaving Age out of the query, this column simply does not show up in the result set.
I referred the query back to the workbook itself - you can obviously choose whatever location you want to store it in.
After the query was created I right clicked on C3 of the Query and picked Parameters to state where I wanted to pick up the parameter value from.
In the dialog box I chose "Get the value from the following cell: =Query!$A$1 " and checked the box next to "Refresh automatically when cell value changes."
So, when I enter "Green" in cell A1 of Query I get the following result set:
Green
Surname First Name City
Green Dallas Dallas
And when I enter "Smith" in cell A1 I get
Smith
Surname First_Name City
Smith Adam Toronto
Smith Warren San Diego
Smith Zulu Pittsburgh
There is no need to clear out old data. Adding or removing columns is just a matter of editing the SQL query. (Right click on C3, choose Edit Query, and then click on the SQL button.)
MS Query is worth knowing about. You can also easily suck in data from MS Access databases this way
