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: How copy all rows that have a given column entry?
Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#2
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
How about DataFilterAutofilter?
Filter on your criterion then copy the results to the clipboard. Gord Dibben MS Excel MVP On Thu, 31 May 2007 10:46:02 -0700, Motown Mick wrote: Do you know a quick and efficient method on how to mark or flag somehow, and copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#3
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Dear Gord,
Thank you for your prompt reply. Unfortunately, I think I need a more detailed response. I am not sure how to "filter on [my] criterion". I did DataFilterAutofilter. A check appeared beside the Autofilter indicator, but nothing else happened. Is there any more you can add to your reply that might provide me the missing information I need to accomplish my task? Mick "Gord Dibben" wrote: How about DataFilterAutofilter? Filter on your criterion then copy the results to the clipboard. Gord Dibben MS Excel MVP On Thu, 31 May 2007 10:46:02 -0700, Motown Mick wrote: Do you know a quick and efficient method on how to mark or flag somehow, and copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#4
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Hi
Assuming your data has headers in row 1. Mark the block of data. DatafilterAutofilter Choose the dropdown on the column required and choose Custom Set the parameters to Greater than and 0 -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Gord, Thank you for your prompt reply. Unfortunately, I think I need a more detailed response. I am not sure how to "filter on [my] criterion". I did DataFilterAutofilter. A check appeared beside the Autofilter indicator, but nothing else happened. Is there any more you can add to your reply that might provide me the missing information I need to accomplish my task? Mick "Gord Dibben" wrote: How about DataFilterAutofilter? Filter on your criterion then copy the results to the clipboard. Gord Dibben MS Excel MVP On Thu, 31 May 2007 10:46:02 -0700, Motown Mick wrote: Do you know a quick and efficient method on how to mark or flag somehow, and copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#5
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Thanks Roger.
That's about it, although OP does mention multiple columns which might give a wrinkle. Wait and see. Gord On Fri, 1 Jun 2007 00:37:09 +0100, "Roger Govier" wrote: Hi Assuming your data has headers in row 1. Mark the block of data. DatafilterAutofilter Choose the dropdown on the column required and choose Custom Set the parameters to Greater than and 0 |
#6
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Dear Roger,
Thanks! I think I've got it now; and also by reading about "filter" on Excel's built-in help. I just clicked a cell in one of the relevant columns, did DataFilterAutofilter, then clicked on the arrow that appears in each column when you do that, clicked "custom", saw that pop-up menu, did "greater than" "0", and that seems to have done the work for me. Only two remaining questions: 1. I'm not sure what you mean by "mark the block of data". Rather than checking for values greater than zero in one column, it would be more ideal for my purposes if I could filter for values greater than zero in more than one column simultaneously. If I understand you correctly, how could I "mark the block of data" I wish to perform the analysis on so as to include more than one column? In other words, how do I filter for data greater than zero in column A, B, and C, and filter out any row that has a value greater than zero in at least one of those columns? 2. Once I have done this, how do I get the worksheet back to normal--i.e., the way it was before I did the filtering? I've been closing the document without saving, and then re-opening it every time I want to play around with the filtering device so as not to destroy the previous results. What a nuisance! Mick "Roger Govier" wrote: Hi Assuming your data has headers in row 1. Mark the block of data. DatafilterAutofilter Choose the dropdown on the column required and choose Custom Set the parameters to Greater than and 0 -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Gord, Thank you for your prompt reply. Unfortunately, I think I need a more detailed response. I am not sure how to "filter on [my] criterion". I did DataFilterAutofilter. A check appeared beside the Autofilter indicator, but nothing else happened. Is there any more you can add to your reply that might provide me the missing information I need to accomplish my task? Mick "Gord Dibben" wrote: How about DataFilterAutofilter? Filter on your criterion then copy the results to the clipboard. Gord Dibben MS Excel MVP On Thu, 31 May 2007 10:46:02 -0700, Motown Mick wrote: Do you know a quick and efficient method on how to mark or flag somehow, and copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#7
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Hi Mick
What I meant was select the complete range of your data before applying Autofilter, then you will get the dropdowns on each column within the range. You can apply filters to as many columns as you wish, each time it will reduce the selection to those rows that match each of the criteria you have applied. Clicking the dropdown on the column again, and selecting All will remove the filter from that particular column. DataFilterAutofilter again, will remove all filters - it is a toggle, either to switch it on or off but this removes the dropdowns altogether. I have dragged a Show All command to my toolbar, which I can click to remove all filters at once, whilst leaving the dropdowns in place. ViewToolbarsCustomiseCommandsData drag the Show All to your toolbar. It appears as Text only, but whilst you are still in Customise mode, you can right click on the Text Show All and choose an icon if you wish. -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Roger, Thanks! I think I've got it now; and also by reading about "filter" on Excel's built-in help. I just clicked a cell in one of the relevant columns, did DataFilterAutofilter, then clicked on the arrow that appears in each column when you do that, clicked "custom", saw that pop-up menu, did "greater than" "0", and that seems to have done the work for me. Only two remaining questions: 1. I'm not sure what you mean by "mark the block of data". Rather than checking for values greater than zero in one column, it would be more ideal for my purposes if I could filter for values greater than zero in more than one column simultaneously. If I understand you correctly, how could I "mark the block of data" I wish to perform the analysis on so as to include more than one column? In other words, how do I filter for data greater than zero in column A, B, and C, and filter out any row that has a value greater than zero in at least one of those columns? 2. Once I have done this, how do I get the worksheet back to normal--i.e., the way it was before I did the filtering? I've been closing the document without saving, and then re-opening it every time I want to play around with the filtering device so as not to destroy the previous results. What a nuisance! Mick "Roger Govier" wrote: Hi Assuming your data has headers in row 1. Mark the block of data. DatafilterAutofilter Choose the dropdown on the column required and choose Custom Set the parameters to Greater than and 0 -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Gord, Thank you for your prompt reply. Unfortunately, I think I need a more detailed response. I am not sure how to "filter on [my] criterion". I did DataFilterAutofilter. A check appeared beside the Autofilter indicator, but nothing else happened. Is there any more you can add to your reply that might provide me the missing information I need to accomplish my task? Mick "Gord Dibben" wrote: How about DataFilterAutofilter? Filter on your criterion then copy the results to the clipboard. Gord Dibben MS Excel MVP On Thu, 31 May 2007 10:46:02 -0700, Motown Mick wrote: Do you know a quick and efficient method on how to mark or flag somehow, and copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#8
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Dear Roger,
I tried holding down the control key, and selecting the columns I wanted to perform the operation on such that they were all highlighted simultaneously; I had to do it this way because they are non-adjascent columns. When I did autofilter, I got an error message that said the command I chose could not be performed with multiple sections, and that I had to select a single range and try again. I suppose I could copy all the relevant columns to another location, arrange them adjascently, and performe the filtering operation on the bunch of them that way. As long as all the pertinent rows matched, it would produce the same desired result. If you know of a quicker easier way to perform this operation on non-adjascent columns, please kindly share your knowledge. It's doesn't matter that much, though, because by clicking "All" on the dropdown menu, as you have explained, and playing with the autofilter function on the other columns, I am able to get the knowledge I am looking for in a fairly expedient fashion. What's going on is I have three columns in which I am looking for entries greater than zero. There is one primary column in which I am looking for entries greater than zero, but I need to check and make sure that the other two columns don't have entries greater than zero in rows in which the primary column is blank. Performing the autofilter on the auxiliary rows, and then checking the primary column for blank spaces accomplishes that task fairly efficiently, now that I know about "All" on the dropdown menu, thanks to you. Your instructions on how to create the "Show All" icon on my toolbar didn't seem to work for me, but does this just do the same thing as selecting "All" on the column dropdown menu as I have been doing? Mick "Roger Govier" wrote: Hi Mick What I meant was select the complete range of your data before applying Autofilter, then you will get the dropdowns on each column within the range. You can apply filters to as many columns as you wish, each time it will reduce the selection to those rows that match each of the criteria you have applied. Clicking the dropdown on the column again, and selecting All will remove the filter from that particular column. DataFilterAutofilter again, will remove all filters - it is a toggle, either to switch it on or off but this removes the dropdowns altogether. I have dragged a Show All command to my toolbar, which I can click to remove all filters at once, whilst leaving the dropdowns in place. ViewToolbarsCustomiseCommandsData drag the Show All to your toolbar. It appears as Text only, but whilst you are still in Customise mode, you can right click on the Text Show All and choose an icon if you wish. -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Roger, Thanks! I think I've got it now; and also by reading about "filter" on Excel's built-in help. I just clicked a cell in one of the relevant columns, did DataFilterAutofilter, then clicked on the arrow that appears in each column when you do that, clicked "custom", saw that pop-up menu, did "greater than" "0", and that seems to have done the work for me. Only two remaining questions: 1. I'm not sure what you mean by "mark the block of data". Rather than checking for values greater than zero in one column, it would be more ideal for my purposes if I could filter for values greater than zero in more than one column simultaneously. If I understand you correctly, how could I "mark the block of data" I wish to perform the analysis on so as to include more than one column? In other words, how do I filter for data greater than zero in column A, B, and C, and filter out any row that has a value greater than zero in at least one of those columns? 2. Once I have done this, how do I get the worksheet back to normal--i.e., the way it was before I did the filtering? I've been closing the document without saving, and then re-opening it every time I want to play around with the filtering device so as not to destroy the previous results. What a nuisance! Mick "Roger Govier" wrote: Hi Assuming your data has headers in row 1. Mark the block of data. DatafilterAutofilter Choose the dropdown on the column required and choose Custom Set the parameters to Greater than and 0 -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Gord, Thank you for your prompt reply. Unfortunately, I think I need a more detailed response. I am not sure how to "filter on [my] criterion". I did DataFilterAutofilter. A check appeared beside the Autofilter indicator, but nothing else happened. Is there any more you can add to your reply that might provide me the missing information I need to accomplish my task? Mick "Gord Dibben" wrote: How about DataFilterAutofilter? Filter on your criterion then copy the results to the clipboard. Gord Dibben MS Excel MVP On Thu, 31 May 2007 10:46:02 -0700, Motown Mick wrote: Do you know a quick and efficient method on how to mark or flag somehow, and copy to the clipboard, all the rows in a worksheet that have an entry in a given column, or set of columns, that conforms to some criterion (greater than zero, in my case)? |
#9
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Hi Mick
I fear I may have mislead you with my instructions. It does not matter whether there are any blank columns within your data, or whether there are columns that you don't wish to filter. I usually mark the complete range then DataFilterautofilter to be sure that the filter is available on all columns that I wish to use, even if some are blank within the range. You can click on any cell within your data and choose DataFilterAutofilter, but if you have any blank columns, then it will think the table only applies up to the first blank column, and will not apply the dropdown to other columns beyond that. Just use the dropdowns on any columns that you wish to filter by. When a filter is applied, the small downward pointing arrow on the filter button, turns Blue, but this can sometimes be difficult to see. Selecting any of these Blue dropdowns and selecting All, removes the filter on that column, but would leave it still applied to any others. The advantage of Show All, is that it will remove all filters that had been applied in one go, taking you back to the full set of data. Try again with adding it to your toolbar. ViewToolbarsCustomiseCommandsCategoriesData At this point, with Data highlit, you should see Commands in the right hand pane of the Dialogue box. Show All is the 5th item down. Whilst holding your let mouse button down on Show All, drag it to the toolbar, to a position between a couple of icons that are there, until the cursor changes to a long black bar, then release the mouse button. It doesn't seem to like it if you try and drop it in a blank area at the end of the toolbar, and maybe this has been your problem. Whilst in this Customise mode, also drag the Autofilter icon to your toolbar, next to the Show All, then click Close. Now when you want to apply Autofilter, just click the Autofilter button. When you have selected a whole range of different filters and have a smaller subset of your data showing, just click Show All to get back to the full set, with all of the filter icons still in place. For more help on Autofilter, take a look at Debra Dalgleish's site http://www.contextures.com/xlautofilter01.html -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Roger, I tried holding down the control key, and selecting the columns I wanted to perform the operation on such that they were all highlighted simultaneously; I had to do it this way because they are non-adjascent columns. When I did autofilter, I got an error message that said the command I chose could not be performed with multiple sections, and that I had to select a single range and try again. I suppose I could copy all the relevant columns to another location, arrange them adjascently, and performe the filtering operation on the bunch of them that way. As long as all the pertinent rows matched, it would produce the same desired result. If you know of a quicker easier way to perform this operation on non-adjascent columns, please kindly share your knowledge. It's doesn't matter that much, though, because by clicking "All" on the dropdown menu, as you have explained, and playing with the autofilter function on the other columns, I am able to get the knowledge I am looking for in a fairly expedient fashion. What's going on is I have three columns in which I am looking for entries greater than zero. There is one primary column in which I am looking for entries greater than zero, but I need to check and make sure that the other two columns don't have entries greater than zero in rows in which the primary column is blank. Performing the autofilter on the auxiliary rows, and then checking the primary column for blank spaces accomplishes that task fairly efficiently, now that I know about "All" on the dropdown menu, thanks to you. Your instructions on how to create the "Show All" icon on my toolbar didn't seem to work for me, but does this just do the same thing as selecting "All" on the column dropdown menu as I have been doing? Mick |
#10
|
|||
|
|||
Excel: How copy all rows that have a given column entry?
Dear Roger:
I was able to create the Show All and Autofilter icons this time. Thanks. Just an aside; the adjascent columns within the range I am working on that I don't wish to filter aren't blank. I highlighted the entire range of columns I wish to perform the filtering operation on. Then I did DataFilterAutofilter and saw that it was only these columns that had the dropdown arrow on them. I clicked the dropdown on the primary column and applied the filter to it. I see that its arrow turned blue. Then I clicked the blue arrow on the primary column, selected "All". This just seems to have undone the filtering operation, but maintained the highlighting on the entire range I highlighted originally. It doesn't seem that the filtering was done on any of the other columns. I think I should be ok with just applying the filter to one column at a time, but with the area highlighted as you've suggested. With the All icon button, I now can undo it quickly and go do the fltering on the auxilliary columns using the Autofilter icon button, and check for any blank spaces in the primary column by simple visual inspection, and do this pretty quickly enough. This sounds like what you are reccommending when you say, "Just use the dropdowns on any columns that you wish to filter by". I guess I was just looking for an even quicker more efficient way to filter all three colums at once for all rows that had at least one entry greater than zero. If you know how to do this, please let me know. Otherwise, I am fine for the time being applying what I've learned from you thus far. Thanks for your help. Mick "Roger Govier" wrote: Hi Mick I fear I may have mislead you with my instructions. It does not matter whether there are any blank columns within your data, or whether there are columns that you don't wish to filter. I usually mark the complete range then DataFilterautofilter to be sure that the filter is available on all columns that I wish to use, even if some are blank within the range. You can click on any cell within your data and choose DataFilterAutofilter, but if you have any blank columns, then it will think the table only applies up to the first blank column, and will not apply the dropdown to other columns beyond that. Just use the dropdowns on any columns that you wish to filter by. When a filter is applied, the small downward pointing arrow on the filter button, turns Blue, but this can sometimes be difficult to see. Selecting any of these Blue dropdowns and selecting All, removes the filter on that column, but would leave it still applied to any others. The advantage of Show All, is that it will remove all filters that had been applied in one go, taking you back to the full set of data. Try again with adding it to your toolbar. ViewToolbarsCustomiseCommandsCategoriesData At this point, with Data highlit, you should see Commands in the right hand pane of the Dialogue box. Show All is the 5th item down. Whilst holding your let mouse button down on Show All, drag it to the toolbar, to a position between a couple of icons that are there, until the cursor changes to a long black bar, then release the mouse button. It doesn't seem to like it if you try and drop it in a blank area at the end of the toolbar, and maybe this has been your problem. Whilst in this Customise mode, also drag the Autofilter icon to your toolbar, next to the Show All, then click Close. Now when you want to apply Autofilter, just click the Autofilter button. When you have selected a whole range of different filters and have a smaller subset of your data showing, just click Show All to get back to the full set, with all of the filter icons still in place. For more help on Autofilter, take a look at Debra Dalgleish's site http://www.contextures.com/xlautofilter01.html -- Regards Roger Govier "Motown Mick" wrote in message ... Dear Roger, I tried holding down the control key, and selecting the columns I wanted to perform the operation on such that they were all highlighted simultaneously; I had to do it this way because they are non-adjascent columns. When I did autofilter, I got an error message that said the command I chose could not be performed with multiple sections, and that I had to select a single range and try again. I suppose I could copy all the relevant columns to another location, arrange them adjascently, and performe the filtering operation on the bunch of them that way. As long as all the pertinent rows matched, it would produce the same desired result. If you know of a quicker easier way to perform this operation on non-adjascent columns, please kindly share your knowledge. It's doesn't matter that much, though, because by clicking "All" on the dropdown menu, as you have explained, and playing with the autofilter function on the other columns, I am able to get the knowledge I am looking for in a fairly expedient fashion. What's going on is I have three columns in which I am looking for entries greater than zero. There is one primary column in which I am looking for entries greater than zero, but I need to check and make sure that the other two columns don't have entries greater than zero in rows in which the primary column is blank. Performing the autofilter on the auxiliary rows, and then checking the primary column for blank spaces accomplishes that task fairly efficiently, now that I know about "All" on the dropdown menu, thanks to you. Your instructions on how to create the "Show All" icon on my toolbar didn't seem to work for me, but does this just do the same thing as selecting "All" on the column dropdown menu as I have been doing? Mick |
Thread Tools | |
Display Modes | |
|
|