Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
How would you modify this to search a row and then copy the column to
another sheet instead of the row? Thanks!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
How would you modify this to search a row and then copy the column to
another sheet instead of the row? Thanks!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy paste Macro in a Protected Sheet | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Search, find, copy muliple cells from new sheet and paste | Excel Programming | |||
search/copy/paste macro | Excel Programming | |||
Search/copy/paste Macro | Excel Programming |