How to macro: 1.Search for string -> 2.Copy row -> 3.Paste in new sheet

[UKNOWN]

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.

[Toppers]

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



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

[Norman Jones]

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



"[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[avveerkar]

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

[anar_baku]

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

[Marty]

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

Thanks!!

[Marty]

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

Thanks!!

[UKNOWN]

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!