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 matc
a certain criteria, say 'Alfred' and make a copy of this ROW and past
it to another sheet/workbook (not important which one).

The problem is that the 'Name' field is in the format of 'RW &
Cottle' or 'M Granger' (just like in the phone book) so as you can se
I CAN'T sort this column by last name and then make a copy of ever
entry that matches the search string.

Instead what I need is a spectacular Macro (or something) to do it fo
me, otherwise i'd be sitting there for weeks copying and pasting (ther
are about 50,000 x 100 files...).

I need to automate this almost completely. I want to enter the name t
search for, have it search for all those records, copy those row
associated with those records, paste them in a new sheet and that'
all! Sounds so easy hey! Well I dare someone to come up with
solution!

I would really appreciate some advice in regards to this problem, as i
could mean a promotion! :)

... and a solution would be absolutely awsome!!

Thanks in advance for any help provided.

Michael Gallaghe

--
M.Gallaghe
-----------------------------------------------------------------------
M.Gallagher's Profile: http://www.excelforum.com/member.php...fo&userid=3129
View this thread: http://www.excelforum.com/showthread.php?threadid=50970

A starter: set ranges etc as required

HTH



Sub FindLastName(srchparm)

Dim ws1 As Worksheet, ws2 As Worksheet, c As Variant

Dim inrng As Range, outrng As Range

Set ws1 = Worksheets("Sheet1")
Set inrng = Range("a2:a500")

Set ws2 = Worksheets("sheet2")
Set outrng = ws2.Range("a2")

ws1.Activate
With inrng
Set c = .Find(srchparm, LookIn:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row).Copy outrng
Set outrng = outrng.Offset(1, 0)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub

Sub MyTest()
FindLastName "Cottle"
End Sub

"M.Gallagher" wrote:


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

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

M.Gallagher Wrote:
Hi,

....

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.

Michael Gallagher

As I understand your problem the Name column has full name - first
name, middle name and surname ( I really do not know 'RW & Cottle'
format ) so obviously you cannot sort on surname. But is it not Ok to
scan the whole name for a string of character say "Alfred" and then
copy the the row where Alfred occurs in the name? Generally the string
that you will want to search for, will invariably be a part of surname
and not first name. What I mean is if you are looking for Granger you
don't expect Granger to be a first name. It is quite unlikely that name
has Granger but it is not part of surname but first name. If this is
acceptable then it would be very easy to handle this.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=509708

My solution is not ideal, but it'll be quick and efficient:

Create another column next to the Name column and name it Surname. Then
in the first cell of that column (let's assume C2) enter this formula:

=RIGHT(B2, LEN(B2)-FIND("Alfred", B2, 1)+1)

all you need to change in the above formula is B2, which is the first
cell that has the name in the list and the "Alfred" which is the
surname. Once you do this copy the formula down for all the rows, now
all the cells in column C will have the surname you were looking for.
Just sort your list and you'll have all the rows you want.

Hope this helps.

Anar


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=509708

How would you modify this to search a row and then copy the column to
another sheet instead of the row?

Thanks!!

How would you modify this to search a row and then copy the column to
another sheet instead of the row?

Thanks!!

Ok, I have been trying so many different ways but all have come up short
on one aspect or another. I'd say this is due to my lack of
understanding of the formulas...

Let me explain EXACTLY what I need:

My table looks like this

NAME.......................ADDRESS................ .....PHONE
etc...
RT & J Smith.............30 Blake Street
JK Nguyen................45 Bronson Road
LE Rogers.................66 Brokern Avenue
JM Le.......................2/17 Regan Road
KR & J Rethers...........44 Telesales Point


The NAME field takes the format 'RT & J Smith' where RT is the first
and middle name initials of one person (husband), the J being the first
initial of the second person (wife) and Smith being the shared surname.

The NAME field stretches for like 50,000 records and I need to filter
all NAMES that contain asian type last names, such as Le or Nguyen.

I have tried using an Advanced Filter, but it is pulling the records
that are like 'LE Rogers', so i've ATTEMPTED to include EXACT so it is
case sensitive and pulls out 'JM Le' and not 'LE Rogers'.

ALL I want to do is identify or copy all the asian name records, and
i'm sure that all I need is an Advanced Filter with Case
Sensitivity...

By the way, I really appreciate all the help you guys have provided,
even though it's causing my head to bleed.

Cheers! 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