If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
excel to read a row, if word 'x' in then put on other sheet ??
I have a large database which i'm continually updating. It has:
Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#2
|
|||
|
|||
Hi Andy100
You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#3
|
|||
|
|||
Hi Andy100
Forgot to add : In the example i filter the Country field for the country Netherlands as you can see. -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#4
|
|||
|
|||
That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#5
|
|||
|
|||
Hi Andy
Try the EasyFilter Add-in first to copy the records to a new sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... That's fine for one filter, but i need to do a lot of filtering and that might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#6
|
|||
|
|||
Using easyfilter i get error message "run-time error 1004 - sort method of
range class failed" I have macros in the spreadsheet, does that matter ? Cheers Andy "Ron de Bruin" wrote in message ... Hi Andy Try the EasyFilter Add-in first to copy the records to a new sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... That's fine for one filter, but i need to do a lot of filtering and that might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#7
|
|||
|
|||
Hi Andy
I like to see your workbook if possible Send it to me private -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... Using easyfilter i get error message "run-time error 1004 - sort method of range class failed" I have macros in the spreadsheet, does that matter ? Cheers Andy "Ron de Bruin" wrote in message ... Hi Andy Try the EasyFilter Add-in first to copy the records to a new sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... That's fine for one filter, but i need to do a lot of filtering and that might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#8
|
|||
|
|||
Thanks Ron, it has a lot of confidential information in there, it relates to
work details addresses, mobile numbers, National Ins numbers. I haven't got it here, it's on my work PC and i daren't send it from there because I would get sacked for sending it over the net !!. They monitor e-mails and i'd get into trouble. Many Thanks for your speedy help, much appreciated. Regards Andrew "Ron de Bruin" wrote in message ... Hi Andy I like to see your workbook if possible Send it to me private -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... Using easyfilter i get error message "run-time error 1004 - sort method of range class failed" I have macros in the spreadsheet, does that matter ? Cheers Andy "Ron de Bruin" wrote in message ... Hi Andy Try the EasyFilter Add-in first to copy the records to a new sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... That's fine for one filter, but i need to do a lot of filtering and that might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#9
|
|||
|
|||
OK
-- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... Thanks Ron, it has a lot of confidential information in there, it relates to work details addresses, mobile numbers, National Ins numbers. I haven't got it here, it's on my work PC and i daren't send it from there because I would get sacked for sending it over the net !!. They monitor e-mails and i'd get into trouble. Many Thanks for your speedy help, much appreciated. Regards Andrew "Ron de Bruin" wrote in message ... Hi Andy I like to see your workbook if possible Send it to me private -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... Using easyfilter i get error message "run-time error 1004 - sort method of range class failed" I have macros in the spreadsheet, does that matter ? Cheers Andy "Ron de Bruin" wrote in message ... Hi Andy Try the EasyFilter Add-in first to copy the records to a new sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... That's fine for one filter, but i need to do a lot of filtering and that might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
#10
|
|||
|
|||
Thanks Ron for your help. I think the problem is to do with the macros that
are in it. I tried it on an excel file i've got here which also has macros, that's how i got that error message. I will try "easy filter" on the works PC to see if it happens on the excel file in question, it may be ok on it. Cheers Andy "Ron de Bruin" wrote in message ... OK -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... Thanks Ron, it has a lot of confidential information in there, it relates to work details addresses, mobile numbers, National Ins numbers. I haven't got it here, it's on my work PC and i daren't send it from there because I would get sacked for sending it over the net !!. They monitor e-mails and i'd get into trouble. Many Thanks for your speedy help, much appreciated. Regards Andrew "Ron de Bruin" wrote in message ... Hi Andy I like to see your workbook if possible Send it to me private -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... Using easyfilter i get error message "run-time error 1004 - sort method of range class failed" I have macros in the spreadsheet, does that matter ? Cheers Andy "Ron de Bruin" wrote in message ... Hi Andy Try the EasyFilter Add-in first to copy the records to a new sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... That's fine for one filter, but i need to do a lot of filtering and that might be a bit cumbersome with filtering ! Thanks anyway Andrew "Ron de Bruin" wrote in message ... Hi Andy100 You can Autofilter and copy See Debra's site also http://www.contextures.com/xlautofilter01.html Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the Country field and choose Netherlands. Copy the filter result 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl c or EditCopy 5) InsertWorksheet 6) Ctrl v or EditPaste 7) Select the sheet with the filter ( Sheet("Netherlands") in my example ) 8) Press Esc 9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Another way is to use EasyFilter http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Andy100" wrote in message ... I have a large database which i'm continually updating. It has: Col A Col B Col C Col D Surnames First names Time In Time Out what i'm wanting to do (in sheet 2) is for excel to look down column A and if it sees the surname "Smith" i want it to copy the whole entry (whole row) into sheet 2. there may be 20 or so queries running at any given time though. That is, i might want it to copy all occurances of surnames "Smith" and "Jones" and "Brown" etc etc etc into sheet 2. not particularly bothered which rows it copies them into as i can do a "sort" on them later. Hope this makes sense !!!! Regards Andrew |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
in an excel macro can you import data from word into a cell? | Trefor | General Discussion | 11 | October 6th, 2005 01:49 PM |
Underscore (_) will not always display in RTF files (Word 2002). | David A Edge | General Discussion | 6 | June 14th, 2005 10:39 AM |
is word perfect compatible with office word? | Noreen | General Discussion | 1 | May 11th, 2005 11:17 PM |
WP Delay Code - Word Equiv | Mike G - Milw, WI | General Discussion | 6 | January 10th, 2005 04:12 PM |
Excel worksheet in Word linked text boxes | Eduardo Oliveira | Page Layout | 0 | January 5th, 2005 11:23 PM |