+ Reply to Thread
Results 1 to 2 of 2

Help - Extracting an entire row that fits a single criteria

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    Calgary
    MS-Off Ver
    Excel 2003
    Posts
    1

    Help - Extracting an entire row that fits a single criteria

    I apologise if this has been answered before but I want back over 7 sheets and couldn't see it.

    Here is my problem.

    I have a spreadsheet that tracks projects and their various states of completeness. I need to go through the sheet and find anywhere that states more or less than a percentage and pull it into another sheet for my boss to look at so that he can determine if he needs to get involved. This might include peoples comments that say "I am about 50% done but waiting for X" My boss needs to know all of the information in all of the columns to judge whether his involvement is necessary. This was easy in the past to do manually but with more projects and less time (something we can all relate to) this spreadsheet is getting too big.

    I would like to have an input cell at the top to say something like <50% and then hit a button to extract all of the rows that fit that criteria. Some weeks he may only need to see the projects that are 25% but some weeks he may need to see anything less than 75%. In the explanation below the Working Sheet is where everyone inputs their data and the Copying Sheet is where I want the information extracted too. I figure it would be easier to insert a row at the top than to try to find the bottom. I can talk out the macro but I can't write it...Here it is:

    find "(contents of the input cell)" in (range)
    Copy row (or columns A - Z in the row that contains the criteria)
    Goto (copying sheet)
    Insert row at the top
    Paste
    Goto (Working sheet)
    continue search for next match
    Copy row
    Goto (copying sheet)
    Insert row at the top
    Paste
    Goto (Working sheet)
    (continue to the end of the range)

    Thanks in advance for your help.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help - Extracting an entire row that fits a single criteria

    Designing your sheet properly makes this a breeze. Burying 50% in the middle of the text string is not good sheet design.

    I would have three fields for this, for example:

    Column M - Status (dropdown with choices like "New,Canceled,In-Progress,On Hold,Complete")

    Column N - Percentage (a dropdown with standard percentages they can choose like, "0%,25%,50%,75%,100%")

    Column O - Comment (A text field where the longhand notes can be entered.)

    With those fields in your sheet(s), you need only turn on the Data > Filter > Autofilter and filter column N by the percentage (less than or equal 50%, for instance) and voila...a filtered list appears. Or filter by the "Status"...or even by both.

    Also, writing a macro to put ALL of those rows onto a sheet for your manager to review is extremely easy once the sheets are designed for easy filtering like this.

    See if you can add this type of standard field to your sheet and then post it up here and we can surely help make that macro for you in short order.
    Last edited by JBeaucaire; 09-21-2009 at 06:42 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1