Looukp Specific Records

[Tony Montana]

Okay here's my problem. I have a table with a few columns that are in no
particular order. For example:
Column A Column B Column C Column D
Name 1 Date 3 Reason
Name 2 Date 4 Reason
Name 1 Date 2 Reason
Name 4 Date 2 Reason
Name 2 Date 4 Reason
Name 1 Date 5 Reason

What I want to do is extract the data and create a new list for each name on
a different sheet but with no gaps so for example:

Column A Column B Column C Column D
Name 1 Date 3 Reason
Name 1 Date 2 Reason
Name 1 Date 5 Reason

Any help at all would be most appreciated.

 

[Gary''s Student]

You can use AutoFilter:

1. click on the column A header and:

Data > Filter > AutoFilter

2. using the pull-down you can display all the rows for any name.
3. copy the visible rows and paste at your destination.

 

[Tony Montana]

The problem is that I want this process to be automated so that the data
looks up what ever I type in without data sorting.

 

[Gary''s Student]

You can perform the task manually with the Macro Recorder turned on. Then
run the recorded macro whenever you update your master table.

 

[Dave Peterson]

I'd keep the data in one worksheet.

Then use data|filter|autofilter to see the info that I want. I think you'll
find that this is the easiest solution.

But if you really, really must. Keep the data in one worksheet and refresh the
worksheets whenever you need (not while doing input--just run the macro when
you're ready).

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm