Hi Michael,
Look at using Excel's Advanced Filter to extract the required data to
another sheet. Use a wildcard criterion , say Arthur*, to extract records
with Arthur as a first name; or *Arthur to find the corresponding last
names.
If you are not familiar with the use of Advanced Filter to perform the
required tasks, see Debra Dalgleish's tutorial at:
http://www.contextures.com/xladvfilter01.html
To automate the process, turn on the macro recorder and perform the
operation manually. This will provide you with code which can be edited to
more generic use.
If you experience problems with this editing, post back with the relevant
code portion.
---
Regards,
Norman
"M.Gallagher"
wrote in message
...
Hi,
I have a spreadsheet with about 50,000 contacts. The columns (fields)
are Name, Address, Phone Number, etc..
I need to search through this spreadsheet for all last names that match
a certain criteria, say 'Alfred' and make a copy of this ROW and paste
it to another sheet/workbook (not important which one).
The problem is that the 'Name' field is in the format of 'RW & T
Cottle' or 'M Granger' (just like in the phone book) so as you can see
I CAN'T sort this column by last name and then make a copy of every
entry that matches the search string.
Instead what I need is a spectacular Macro (or something) to do it for
me, otherwise i'd be sitting there for weeks copying and pasting (there
are about 50,000 x 100 files...).
I need to automate this almost completely. I want to enter the name to
search for, have it search for all those records, copy those rows
associated with those records, paste them in a new sheet and that's
all! Sounds so easy hey! Well I dare someone to come up with a
solution! 
I would really appreciate some advice in regards to this problem, as it
could mean a promotion! :)
.. and a solution would be absolutely awsome!!
Thanks in advance for any help provided.
Michael Gallagher
--
M.Gallagher
------------------------------------------------------------------------
M.Gallagher's Profile:
http://www.excelforum.com/member.php...o&userid=31299
View this thread: http://www.excelforum.com/showthread...hreadid=509708